PowerBI and OLAP (MDX) Hierarchy will not filter YTD for partial year

[Hikmer]

I am struggling with PowerBI and showing Current Year to date calcualtions that only refelct yesterdays date and previous days BUT not future days when doing a Year over Year period calcualtion.  At the moment, when reporting from our cube I get the total Previous Year sum instead of a partial PYTD which woudl be a more meaningful calucaltion.  Excel allows me to filter on a calendar day in the hierachy and will show the calcualtion correctly.  However, PowerBI doesn't have this mechanism.  How do people handle partial YTD and PYTD caluclations when using MDX based OLAP cubes?  It's a bit complex so I hope I am explaining this correctly.

[v-sihou-msft · Accepted solution]

@Hikmer

To achieve same logic like TOTALYTD() for SAMEPERIODLASTYEAR() in DAX, you can use the PARALLELPERIOD() and OPENINGPERIOD() to apply the data range from first day of previous day to same day last year in your calculation with MDX. Please refer to my sample below:

with member [Previous Year YTD] as
sum(
openingperiod(
[Date].[Calendar].[Date],
ancestor(
parallelperiod([Date].[Calendar].[Calendar Year],1, [Date].[Calendar].currentmember)
,[Date].[Calendar].[Calendar Year]
)
)
:
parallelperiod([Date].[Calendar].[Calendar Year],1, [Date].[Calendar].currentmember)
,[Measures].[Internet Sales Amount])
select {[Measures].[Internet Sales Amount],[x]} on 0,
[Date].[Calendar].[Date].members on 1
from
[Adventure Works]

Regards,