Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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
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
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
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
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |