Forum Discussion
Multi-level rolling average
Hi everyone,
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'
)))
The other 2 options to calculate the rolling average did not work for the totals (see topic https://community.powerbi.com/t5/Desktop/date-type-not-recognized-as-such-in-DAX/m-p/490127#M228387)
the problem is.... this measure does not work on individual FSI items:
Believe me, there's no way that 6 months average for Import duties is the same as how much we are spending on supplies. Trump has not import-dutied us out of business yet.
edit: ... we also have different locations that people would like to filter and see specific rolling averages for.
Probably to do with that all(sheet) at the end, see if allexcept(sheet, sheet[FSI]) does anything
5 Replies
Probably to do with that all(sheet) at the end, see if allexcept(sheet, sheet[FSI]) does anything
Olia You need to do ALLEXCEPT(FSI ITEM) to calculate rolling average. Also you could use variable to make it cleaner.
Rolling Avg 6m =
VAR Opt1 = 1st calc
VAR Opt2 = 2nd calc
RETURN IF(COUNTROWS(values('Sheet'[Month]))=1, Opt1, Opt2)
Olia you will need to use allexcept(country code) for that agg as well. I was suggesting using variable to writer cleaner formulas.
