Multi-level rolling average

[Olia]

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.

[Anonymous]

@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]

@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?

@Anonymous I'm sorry, I don't understand.

[Anonymous]

@Olia you will need to use allexcept(country code)  for that agg as well. I was suggesting using variable to writer cleaner formulas.

https://msdn.microsoft.com/en-us/query-bi/dax/var-dax

[Olia]

@Anonymous oh I see. that seems a bit too complicated for me right now, as I don't understand anything that I'm writing yet. I'm just copying stuff over from forums.

edit: omg omg omg, it's working!!!! Thank you @jthomson and @Anonymous for your help!!!

[jthomson · Accepted solution]

Probably to do with that all(sheet) at the end, see if allexcept(sheet, sheet[FSI]) does anything