Showing results for 
Search instead for 
Did you mean: 
New Member

Power BI Live connection to MD cube - PERIODSTODATE not working

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.






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

There is also a similar thread for your reference.


Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.


Helpful resources

PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors