Forum Discussion
Calculation group does not properly aggregate measures created using DIVIDE()
I finally solved it on my own. What I was looking for was a way to get DAX to follow the default aggregation for the measure in the database rather than simply summing everything. I finally realized that CALCULATE does this if there is no instruction to do otherwise. The correct code for a rolling 12 where the 12-month period ends with the previous month (to leave out a partial current month) is:
CALCULATE (
SELECTEDMEASURE(),
PARALLELPERIOD(DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -12, MONTH ), -1, MONTH)
)If you want to include the current month in the 12-month period, just take out the PARALLELPERIOD:
CALCULATE (
SELECTEDMEASURE(),
DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -12, MONTH )
)I am not sure why the original developer was forcing a SUMX only in the R12 calculation group item but it may have been due to an earlier bug. Anyway it works fine now.
Thanks!
I finally solved it on my own. What I was looking for was a way to get DAX to follow the default aggregation for the measure in the database rather than simply summing everything. I finally realized that CALCULATE does this if there is no instruction to do otherwise. The correct code for a rolling 12 where the 12-month period ends with the previous month (to leave out a partial current month) is:
CALCULATE (
SELECTEDMEASURE(),
PARALLELPERIOD(DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -12, MONTH ), -1, MONTH)
)
If you want to include the current month in the 12-month period, just take out the PARALLELPERIOD:
CALCULATE (
SELECTEDMEASURE(),
DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -12, MONTH )
)
I am not sure why the original developer was forcing a SUMX only in the R12 calculation group item but it may have been due to an earlier bug. Anyway it works fine now.
Thanks!