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

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.