Forum Discussion
Trailing Average
Hi all,
I have been trying to move all reports calculations from excel spreadsheets, and completely using Power BI. I'm having an issue calculating trailing 12 months trailing average in Power BI, I can calculate the trailing average in excel, but the researches I'm doing in power BI is leading me to rolling average. Currently I import the calculations from excel. As shown in the diagram below, I want to calculate rolling average and trailing average, I need help calculating the "Trailing Average".
Hi Masikonde ,
I'd like to suggest you do unpivot columns on your table, then you can write a measure formula to loop all fields for rolling calculations based on its attribute.
Measure = VAR currDate = MAX ( Table[Date] ) RETURN CALCULATE ( AVERAGE ( Table[Value] ), FILTER ( ALLSELECTED ( Table ), [Date] <= currDate ), VALUES ( Table[Attribute] ) )Regards,
Xiaoxin Sheng
3 Replies
Hi Masikonde ,
I'd like to suggest you do unpivot columns on your table, then you can write a measure formula to loop all fields for rolling calculations based on its attribute.
Measure = VAR currDate = MAX ( Table[Date] ) RETURN CALCULATE ( AVERAGE ( Table[Value] ), FILTER ( ALLSELECTED ( Table ), [Date] <= currDate ), VALUES ( Table[Attribute] ) )Regards,
Xiaoxin Sheng
The process worked on Excel, but could not work on power BI. I think because I'm trying to look for average from defferent departments.
Hi Masikonde ,
You need to go to 'query editor' to do 'unpivot column' feature on your table fields, then save change and return to the 'data view' side to write sample formula.
If you still confused about how to use it, please share a pbix file with some sample data for test and coding formula.
Regards,Xiaoxin Sheng
