date hierarchy mdx calculated measures

[ADP007]

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]

)

Any help is more than welcome

Regards

[v-shex-msft]

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

[ADP007]

HI Xiaoxin,

Thanks for your feedback.

How would you manually calculate the filtered date range?

Thanks

David

[v-shex-msft]

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

[ADP007]

Hi Xiaoxin,

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

Regards

David

[v-shex-msft]

Hi @ADP007,

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

Regards,

Xiaoxin Sheng

[ADP007]

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

[v-shex-msft]

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