Search code examples
reporting-servicesssasmdx

Display Measure depending on Date, combine two measure from different dates

Background: I am preparng a dataset for a report. To simplify lets say it is two measures, one Category and a time dimension. The main Report Parameter is todays date. I would like to see the first Measure from yesterday (Value 000) and the second measures from the day before yesterday (Value 001). Because previous days data is not available yet for Value 001.

Lets assume 2020-04-27 is today (passed on from the Reporting Tool SSRS).

AS-IS

SELECT {[Measures].[Value 000], [Measures].[Value 001]} ON COLUMNS
    , ( {[DIM Category].[Category].&[1], [DIM Category].[Category].&[2]}, 
    [DIM Date].[Y-M-D ISO].[Date].[2020-04-27].LAG(2): [DIM Date].[Y-M-D ISO].[Date].[2020-04-27].LAG(1)
    )
 ON ROWS
FROM [My_Cube]

Output:

                        [Value 000]     [Value 001]     
Category 1  2020-04-25      88              16
Category 1  2020-04-26      89              (null)
Category 2  2020-04-25      90              14
Category 2  2020-04-26      92              (null)

I tried two MDX-Queries for each date and combined the Sets in the Reporting tool but I am wondering if there is a comfortable way to do that in one MDX query. The following would be the desired output.

To-Be

Output:

                [Value 000]     [Value 001]                 
Category 1          89              16          (Value from day before, for Value 0001) 
Category 2          92              14

The Date doesn't need to be shown in the output (but can be)

Solution

  • You can solve the issue by using calculated measures. Follwoing example is based on adventureworks . I want to rebuild your senario for Jan 23 2013

    The initial query is there to show values for yesterday abd day before with member measures.Yesterday as sum([Date].[Date].currentmember.lag(1),[Measures].[Internet Sales Amount])

    member measures.DayBefore 
as 
sum([Date].[Date].currentmember.lag(2),[Measures].[Internet Sales Amount])
select 
{
[Measures].[Internet Sales Amount],measures.Yesterday ,measures.DayBefore
}
on columns,

{
([Product].[Category].[Category],[Date].[Date].&[20130120]:[Date].[Date].&[20130123])
}
on rows 
from 
[Adventure Works]
where [Date].[Date].&[20130123]

    Result

    enter image description here

    Now lets remove the date from the rows and put it in where 

    with 
member measures.Yesterday 
as 
sum([Date].[Date].currentmember.lag(1),[Measures].[Internet Sales Amount])

member measures.DayBefore 
as 
sum([Date].[Date].currentmember.lag(2),[Measures].[Internet Sales Amount])
select 
{
[Measures].[Internet Sales Amount],measures.Yesterday ,measures.DayBefore
}
on columns,

{
([Product].[Category].[Category])
}
on rows 
from 
[Adventure Works]
where [Date].[Date].&[20130123]

    Result

    enter image description here