DAX Query to MDX Cube

[Phil_Seamark]

I would like to display the most recent 30 days of sales data along with the values for the same 30 days for the previous year.

I have an attribute in my date dimension called [Days from Today] which recalculates every day.  I can set this in Power BI to be "Less than 30" which works nicely.

My MDX cube returns the correct value for [Sales] for each of the recent 30 days.  However my [Sales Previous Year] calculated measure returns blank.  If I do not use the [Days from Today] filter at all, both sales measures return the correct values, however as I have data going back to 2008, I get more data than I want.

It appears the [Days from Today] attribute is filtering out everything prior to the 30 days which makes MDX functions like LAG(364) no good.

Any suggestions???

[Phil_Seamark]

The idea being, is that the "recent 30 days" is a rolling 30 day range.  So if I check tomorrow, then it will include a new day while dropping out the older day.

The x-axis should be individual days, and showing a calculated measure that displays the value for the same day the previous year.

The datasource is an MDX Cube.

It seems the DAX query that is generated overfilters and kills the data outside the [Days from Today] by possibly using a sub-cube type statement.  This is overkill and would love to know how anyone else is getting around this.

[SqlJason]

Would it be possible for you to pass on thecalculation for the measure, so that I can try to simulate here what is happening?

And regarding the environment, my understanding is that you have a cube with a calculated measure for last 30 days, and you are just using the measure as it is in Power BI. Can you confirm my understanding?

[Phil_Seamark]

My Date dimension is based off a view in the database table.  One of the columns in the view is [Days from Today] which performs a DATEDIFF function to give each date in my dimension table an integer value.  Eg. yesterday = 1, the day before =2 etc.  This technique seems to provide a nice sliding date-range approach so users can simply refresh their reports/dashboards to see recent data without having to fiddle with filters.  I run a "ProcessUpdate" on my date dimension at the start of each day.

In Power BI, I like to set it to be "less than 31".  I capture the DAXQuery and run it using DAXStudio locally.  It seems to kill any data older than the filter setting.  I would have thought a Scoped measure in the MDX cube would still return a value but it won't

I've tried using LookupCube, LinkMember, StrToMember etc but it's like the session is scoping the cube down to a sub-cube so will never return a value.

Does this make sense?

The MDX Calculated measure is as follows:

CREATE MEMBER CURRENTCUBE.[Measures].[Purchases Previous Year]  AS NULL,

FORMAT_STRING = "#,###";  

SCOPE ([Purchases Previous Year]);  

    SCOPE([Dates].[Year - Week - Day].[Year]); 
        this = IIF([Purchases] <> 0,([Dates].[Year - Week - Day].currentmember.lag(1),[Purchases]),NULL); 
    END SCOPE; 

    SCOPE([Dates].[Year - Week - Day].[Week]); 
        this = IIF([Purchases] <> 0,([Dates].[Year - Week - Day].currentmember.lag(52),[Purchases]),NULL); 
    END SCOPE; 

    SCOPE([Dates].[Year - Week - Day].[Day]); 
        this = IIF([Purchases] <> 0,([Dates].[Year - Week - Day].currentmember.lag(52*7),[Purchases]),NULL); 
    END SCOPE; 

    format_string(this) = "#,###";   

END SCOPE; 

[Phil_Seamark]

Built a cut down MDX cube and finding this is also happening when I try to use the [Days from Today] directly.   

The backup solution is to use Dynamic named sets of date ranges.  However Power BI doesn't allow you to use Named Sets when building reports.  

[Phil_Seamark · Accepted solution]

Found my issue. When I was trying to use calculated measures that referenced outside the date range eg. Year on Year comparison, then would not work if I used attributes of my date dimension. I needed to change the calculated measures to use the Key on the Date dimension.  The problem I have is I'm using integer data types through my FACT tables which don't scale in Power BI visuals. I just have to convert to use the 4 byte DATE data type and all will be good.

[mattkarriker]

Phil

Can you expand a little more on your solutin to this issue?  I am experienceing the exact same issue and have been looking at this for 3 days now.  When you say you needed to have the Calculated Measures use the Key of the Date Dimension are you refering to the Scope statements for [Purchases Previous Year]?  Are you saying other than using expressions like [Dates].[Year - Week - Day].currentmember.lag(1) you are using [Dates].[Key].members?