I'm trying to calculate 6 months average amount per financial statement item (FSI), which is basically a multi-level thing (as you have a level Wages for example, and below that level that splits Wages into Direct and Indirect, and then those get splitted into even more detailed items).
I have found 1 measure DAX that works on the agregate level, showing the correct numbers for the line item "total" per month.
Rolling average 6m GC = if(COUNTROWS(values('Sheet'[Month]))=1, calculate( sum('Sheet'[Group Amount]) / COUNTROWS(values('Sheet'[Month])), DATESBETWEEN( 'Sheet'[Date], FIRSTDATE(PARALLELPERIOD('Sheet'[Date], -5, MONTH)), LASTDATE(parallelperiod('Sheet'[Date],0, MONTH)) ),all('Sheet' )))
@jthomson yes, now it works on month and FSI level. but it's not working on Country-level or agregate level yet. Sadly just adding ,"Sheet"[Country_code" to that allexcept formula does not seem to work. suggestions?