I am setting up a multidimensional cube in SSAS which will be queried in PowerBI and Excel.
I need a calculated measure (MDX) similar to YTD/MTD/QTD for Sales but the cumulative function needs to be dependent on the date filter (period level, eg. 202111) used in the reporting tool.
For example, if periods 202102, 202103 and 202104 are chosen, the measure need to aggregate the numbers for each month starting from 0.
Feb Sales: 100, measure shows 100.
March Sales: 200, meaure shows 300.
April Sales: 100, measure shows 400.
The following DAX measure does the trick. I need the equivalent in MDX
Cumulative sales =
IFERROR(
CALCULATE(
[Sales],
FILTER(
ALLSELECTED(Date[Period]),
Date[Period] <= MAX(Date[Period])
)
),
BLANK()
)