cancel
Showing results for
Did you mean:
Helper III

## 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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User I

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

5 REPLIES 5
Super User I

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

Solution Specialist

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

Helper III

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

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

Solution Specialist

@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

Helper III

@mnayar 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 @mnayar for your help!!!

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform October Community Highlights

Check out the top community contributors across all of the communities

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors