Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Rolling average on multiple categories

I'm trying to calculate 7 day rolling average. The image shows the result calculated in Excel. Filtering the data using the two slicers would adjust the sum and hence the rolling average. Excel file ...
Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    Hi Anonymous,

     

    Try the following measure:

     

    Rolling Average = 
CALCULATE (
    AVERAGEX (
        FILTER (
            SUMMARIZE (
                ALL ( Raw_Data[Date]; Raw_Data[Leads] );
                Raw_Data[Date];
                "Leads_Sums"; SUM ( Raw_Data[Leads] )
            );
            Raw_Data[Date]
                >= MAX ( Raw_Data[Date] ) - 6
                && Raw_Data[Date] <= MAX ( Raw_Data[Date] )
        );
        [Leads_Sums]
    )
)

    Should work if you add the categories to your filters or visuals also.

     

    Regards,

    MFelix

Ashish_Mathur's avatar
Ashish_Mathur
Super User
7 years ago

Hi,

You may download my PBI file from here.

Hope this helps.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Ashish_Mathur  

     

    Thanks for the effort. Your solution works too, however, it breaks if I use Cat1 or Cat2 in the visual. It keeps working if i use them as filters though.

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      7 years ago

      Hi,

      Could you show that in a screenshot please?