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

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