Power BI Live connection to MD cube - PERIODSTODATE not working

[-danmajerle-]

Hi All,

We have an existing MD cube that has a closing balance style measure for our Fiscal date hierarchy in a format like:

SUM(PERIODSTODATE([Date].[Fiscal Hierarchy].[(All)], [Date].[Fiscal Hierarchy]),[Measures].[Amount])

In Excel, this works well with a date filter, i.e. the measure shows the aggregation up to the date selected.

In Power BI, if we do something similar, e.g. Have a date slicer acting like an Excel filter, the measure only shows the value for that date alone. Looking at the DAX executed, it appears that the KEEPFILTERS is restricting the cube down to that one date only and will not traverse the date hierarchy to aggregate the date as the Excel MDX query does.

Has anyone else seen this behaviour and is there a workaround for it?

Thanks for considering and sorry if this has been asked before. We couldn't find any other examples.

[v-yuezhe-msft]

@-danmajerle-,

Please follow the guide in this blog to convert the above MDX to DAX.

There is also a similar thread for your reference.
https://community.powerbi.com/t5/Desktop/Converting-MDX-calculations-to-DAX-Inventory-ending-balance...

Regards,
Lydia

[-danmajerle-]

Hi. Thanks for that.

The cube we are connecting to is multi-dimensional, so we dont have the option to change the measure to DAX.

I think the issue is with how Power BI interacts with the MD cube.