As the situation explained in my previous post, last-non-empty-value-in-dax we might have to give the last available value of say balance amount in my bank account. So for that we need to travel back in time axis to get the last non-empty value for a given measure at any point in time.
There are couple of methods you can follow depending on the scenario:
Method 1: This is the simple recursive query which gets the last available data from the cube.
IIF(
ISEMPTY([Measures].[Amount]),
FROM (SELECT [Product].[Product Name].&[Bike] ON 0
FROM [Project])
One point to remember is the cube should atleast have data for one dimension combination that we are going to query on. If we need the last available Product amount, then for every product in the product dimension we need Amount for atleast one day in time dimension . Else the Measure.ProdAmount goes into infinite loop.
If there is no data for a given product for any date in time dimension then the Measure.ProdAmountLA returns value as NULL.
NONEMPTY(
[FiscalPeriod].[FiscalMonth].CHILDREN*[Measures].[Amount]),1).ITEM(0)
Method 3: There are scenarios were the NULL values in cube is automatically replaced as 0 by default. This happens when the NULLProcessing property of the measure is set as 'Automatic' instead of 'Preserve'. http://msdn.microsoft.com/en-us/library/ms345138(SQL.90).aspx
In this case we cannot use Nonempty function as it will return null values as they are replaced as 0 in the cube. So we will filter out data which are not 0
MEMBER [Measures].[ProdAmountLA] AS
TAIL(
Filter(
[FiscalPeriod].[FiscalMonth].CHILDREN *[Measures].[Amount]
More Details: http://cwebbbi.wordpress.com/2011/03/24/last-ever-non-empty-a-new-fast-mdx-approach/
There are couple of methods you can follow depending on the scenario:
Method 1: This is the simple recursive query which gets the last available data from the cube.
WITH
MEMBER
[Measures].[ProdAmount] ASIIF(
ISEMPTY([Measures].[Amount]),
([FiscalPeriod].[Calendar].PREVMEMBER,[Measures].[ProdAmount])
,[Measures].[Amount])
SELECT [Measures].[ProdAmount]
on 0,
[FiscalPeriod].[Calendar].[Fiscal
Month] on 1FROM (SELECT [Product].[Product Name].&[Bike] ON 0
FROM [Project])
One point to remember is the cube should atleast have data for one dimension combination that we are going to query on. If we need the last available Product amount, then for every product in the product dimension we need Amount for atleast one day in time dimension . Else the Measure.ProdAmount goes into infinite loop.
Method 2. You can override the above problem using Nonempty and Tail function: The following query gives the latest product amount given for a particular product for the given year. Here the calculated measure, ProdAmount firstly calculates all the nonempty month, measure set. And then takes the latest month, amount set for the given Product.
MEMBER [Measures].[ProdAmountLA] AS
TAIL( NONEMPTY(
[FiscalPeriod].[FiscalMonth].CHILDREN*[Measures].[Amount]),1).ITEM(0)
Method 3: There are scenarios were the NULL values in cube is automatically replaced as 0 by default. This happens when the NULLProcessing property of the measure is set as 'Automatic' instead of 'Preserve'. http://msdn.microsoft.com/en-us/library/ms345138(SQL.90).aspx
In this case we cannot use Nonempty function as it will return null values as they are replaced as 0 in the cube. So we will filter out data which are not 0
MEMBER [Measures].[ProdAmountLA] AS
TAIL(
Filter(
[FiscalPeriod].[FiscalMonth].CHILDREN *[Measures].[Amount]
,[Measures].[Amount] <> 0),1).ITEM(0)
More Details: http://cwebbbi.wordpress.com/2011/03/24/last-ever-non-empty-a-new-fast-mdx-approach/
