cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
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:

 

FSI.png

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
Super User I

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

View solution in original post

5 REPLIES 5
Highlighted
Super User I
Super User I

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

View solution in original post

Highlighted
Solution Specialist
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)

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

Highlighted

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

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