cancel
Showing results for
Did you mean:
Highlighted
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
Highlighted
Super User I

Re: Multi-level rolling average

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

5 REPLIES 5
Highlighted
Super User I

Re: Multi-level rolling average

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

Highlighted
Solution Specialist

Re: Multi-level rolling average

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

Highlighted
Helper III

Re: Multi-level rolling average

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

Highlighted
Solution Specialist

Re: Multi-level rolling average

@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

Highlighted
Helper III

Re: Multi-level rolling average

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

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021