Rolling average on multiple categories

[Anonymous]

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. 

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

@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]

Hi,

Could you show that in a screenshot please?

[ChandeepChhabra]

@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

[MFelix · Accepted solution]

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]

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