Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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 =
sum('Sheet'[Group Amount]) / COUNTROWS(values('Sheet'[Month])),
-5, MONTH)),
LASTDATE(parallelperiod('Sheet'[Date],0, MONTH))


The other 2 options to calculate the rolling average did not work for the totals (see topic


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.

Solution Sage
Solution Sage

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

View solution in original post

Not applicable

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


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

Not applicable

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

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

Solution Sage
Solution Sage

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

Helpful resources

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.


Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors