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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Olia
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
jthomson
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

5 REPLIES 5
Anonymous
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.

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

 

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

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.