4 Weeks Rolling Average - Power BI

Hey guys!

In this post we will see how we can do a 4 weeks rolling average. In another post (Here) I have elaborated what a rolling average is and how it's easy to do it by Year, month and quarter. But since power bi doesn't have week level I will show you a workaround in this article. 

Also the major challenge with using the quick measure for rolling average is that you can't use the data without the date hierarchy but with this approach we can use date in any format we need easily. 

Problem Statement: 

Get the 4 weeks rolling average of resolution time. (support date)

Sample data:

I prepared a sample data like this from 01-Jan-2020 to 07-april-2021 with =RandBetween(0.1,100)/57 function in column C to get some randome decimal values as resolution time

Solution:

While loading the data

 1. I created an extra column

WeekStart=Date.StartOfWeek([Created Date],Day.Sunday)

This column results in the start date of the week for the created date. Change the type to Date in Transform->DataType: Date

2. Created another column with the following formula to get the start date 28 days (4 weeks ago)

4weeksagoWeekStart =Date.StartOfWeek(Date.AddDays([Created Date],-28),Day.Sunday)

In this example we are taking 4 weeks rolling average with last completed week. In case you have a use case for 3 weeks change the -28 to -21 in the formula above.

 Load the data.

After loading:

Create the following measures:

1. Average Resolution time is the avg of the column you want to create. In case you want to apply filter on the dataset for the average like exclude all cases with status=cancelled you can use CALCULATE(avg,filter)

avgResolutionTime = AVERAGE(Sheet1[Resolution Time])

2. Running Total of the average. To create this use the following formula 

Calculate( avgmeasurecreatedin1,Filter(all(Table),weekstartdate<=max(weekstartdate) && weekstartdate<dateadd(weekstartdate,-28,days)

This calculates the avg of last 4 weeks and puts it against the start of the next week. 

avgResolutionTime running total in WeekStart =

CALCULATE(

    [avgResolutionTime],

    FILTER(

        all(Sheet1),

    Sheet1[Created Date]>=max(Sheet1[4weeksagoWeekStart]) && Sheet1[Created Date]<max(Sheet1[WeekStart])

    )

)

In this example we consider last completed week for every week start. If you want to include the current week create a next week start while loading with adddays(Date,+7,days) so you get the start of next week. Also create 3 weeks ago start by using -21 instead of -28. And Change the above formula to Sheet1[Created Date]>=max(Sheet1[3weeksagoWeekStart]) && Sheet1[Created Date]<max(Sheet1[NExtWeekStart])

Explaining the above formula

Lets take an example to understand the formula. If we want the 4 weeks rolling average on 31st  of May 2020 (Sunday) the rolling average must be for the dates between 03 May (sunday) to 30 May saturday. 

Week 1 May 03- May 09

Week 2 May 10 to May 16

Week 3 May 17 to May 23

Week 4 May 24 to May 30

So against 31-May-2020 we need to have Avg(ResolutionTime) from May 03-May 30

For 31-May-2020 

max(Sheet1[4weeksagoWeekStart]) is May 03

max(Sheet1[WeekStart])) is May 31

So we are averaging resolutiontime in this time range which results in rolling 4 weeks average. 

Screenshot of the 4 weeks in consideration for value against 31st May. Average is 0.99

Screenshot of rolling average calculated with the above formula: Value =0.99 

A thumbs up, if this works, motivates me to write more on this blog. Do follow to get notified when i post new tips and tricks.