Forum Discussion

Masikonde's avatar
Masikonde
Frequent Visitor
6 years ago
Solved

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...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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.

    Unpivot columns (Power Query)

    Measure =
VAR currDate =
    MAX ( Table[Date] )
RETURN
    CALCULATE (
        AVERAGE ( Table[Value] ),
        FILTER ( ALLSELECTED ( Table ), [Date] <= currDate ),
        VALUES ( Table[Attribute] )
    )

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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.

Unpivot columns (Power Query)

Measure =
VAR currDate =
    MAX ( Table[Date] )
RETURN
    CALCULATE (
        AVERAGE ( Table[Value] ),
        FILTER ( ALLSELECTED ( Table ), [Date] <= currDate ),
        VALUES ( Table[Attribute] )
    )

Regards,

Xiaoxin Sheng

  • Masikonde's avatar
    Masikonde
    Frequent Visitor
    6 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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