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 with raw data is available at the below link:

https://drive.google.com/file/d/1B8Wqvh9CUeCz0uRV_q813BmLk04NQvq0/view

 

Can someone please help achieve this in PBI? Any help would be greatly appreciated. 

 

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

6 Replies

  • MFelix's avatar
    MFelix
    Super User
    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      MFelix  thank you so much. This works perfectly. Appreciate it.

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

  • ChandeepChhabra's avatar
    ChandeepChhabra
    Impactful Individual
    7 years ago

    Anonymous : Try this measure

     

    7Day Rolling Avg :=
VAR sevendates =
    DATESBETWEEN ( Data[Date], MIN ( Data[Date] ) - 6, MIN ( Data[Date] ) )
RETURN
    IF (
        [Total Leads] <> BLANK ()
            && COUNTROWS ( sevendates ) = 7,
        DIVIDE ( CALCULATE ( [Total Leads], sevendates ), COUNTROWS ( sevendates ) )
    )

    Here is the result

    Download the excel file 

     

    Ideally you should also create a calendar table and drive the measure from there.

    Thanks