Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ADP007
Helper IV
Helper IV

date hierarchy mdx calculated measures

HI, I'm trying to reproduce in PowerBI something that works fine in an Excel PowerPivot when using calculated MDX measures with a date hiearchy. Doesn't work with slicers but works with matrix visualization.

 

In the examples:

 

Sale Net Net shows the amount for 2016/03

Sale Net Net YTD shows total for 2016/01 to 2016/03

Sale Net Net FY shows total for 2016

 

Sale Net Net Y-1 shows the amount for 2015/03

Sale Net Net YTD-1 shows total for 2015/01 to 2015/03

Sale Net Net FY-1 shows total for 2015

 

My measures YTD (year to date) FY (full year) and the same for previous year always show the total values for a year.

 

MDX statement for [Sale Net Net Y-1]

 

AGGREGATE(

    PARALLELPERIOD(

        [Calendar].[Hierarchy].[Year],

        1,

        [Calendar].[Hierarchy].CurrentMember

    )

    , [Measures].[Sale Net Net]

)

 

4-04-2017 14-46-28.png

 

 

Any help is more than welcome

Regards

 

 

 

 

7 REPLIES 7
v-shex-msft
Community Support
Community Support

Hi @ADP007,

 

I'm not so sure why theissue appear, but I think it may related to the custom funciton, perhaps you can contact to the author for more support.

 

BTW, you can also try to manual calculate the filtered date range and test again, I think the issue may related to the compatibility of the custom visual and dax functions.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

HI Xiaoxin,

 

Thanks for your feedback.

How would you manually calculate the filtered date range?

 

Thanks

David

Hi @ADP007,

 

>>How would you manually calculate the filtered date range?

It means you have to manually calculate the date range in your filter formula, for example:

 

Calculate=
var currDate=max(Table[Date])
return
Calculate(formula, FILTER(ALL(Table),[Date]>=Date(Year(currDate)-1,Month(currDate),Day(currDate))&&[Date]<=currDate))

Notice: the bold part is the calculation of date range.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi Xiaoxin,

 

Are you sure this works while connected to SSAS (molap cube) ?

 

Regards

David

Hi @ADP007,

 

SSAS Import mode support, live mode can use measure and calculate column.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

HI again,

 

I'm using live connect so this will not work for me.

Would you recommend using import mode and scheduling a refresh of the document on a daily basis. This way I could use what you recommend?

 

Many thanks

David Peirce

Hi @ADP007,

 

Import mode will break the relationships and measure. In my opinion, you can create and calculate these at service side.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.