cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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.

 

 

 

2 REPLIES 2
Highlighted
Microsoft
Microsoft

Re: Power BI Live connection to MD cube - PERIODSTODATE not working

@-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

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.
Highlighted
New Member

Re: Power BI Live connection to MD cube - PERIODSTODATE not working

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

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Kudoed Authors