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
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors