powerbimoving-averagedayofweek

Moving average for day of week in Power BI


Suppose the following scenario:

I have several measurements coming in daily from an energy company. Each measurement comes from a user and they can be in different states. I have, then, in a simplified form, a table like:

Date State Measurement
22/05/2024 DF 10
22/05/2024 DF 72
22/05/2024 MG 2
22/05/2024 RJ 22
15/05/2024 DF 99
15/05/2024 MG 33
15/05/2024 MG 88
08/05/2024 DF 33
08/05/2024 RJ 12
08/05/2024 RJ 77
01/05/2024 DF 88
01/05/2024 DF 1
01/05/2024 DF 32
24/04/2024 MG 22
24/04/2024 RJ 55
24/04/2024 DF 10

I want to build a Power BI where I will have slicer filters by state and I want to obtain the variation of the average of one day with the average of the measurements for the same day of the week, in the last four weeks.

So, if I have the average for May 22, which is a Wednesday, I want the average of the measurements for May 15, May 8, May 1, and April 24 (Wednesdays).

In the above example, there are some possibilities:

1 - Not selecting any state filter: Thus, assuming for the study day 22/05, I will calculate the average for 22/05 for all measurements of all states (obtaining a single average) and calculate the average of the last 4 weeks, for the same day of the week of 22/05 for all states (obtaining a single average).

2 - Selecting one state: The average should be calculated only for the selected state on 22/05 and the average of the last 4 weeks, for the same day of the week of 22/05 only for the selected state.

3 - Selecting more than one state: The average should be calculated only for the selected states on 22/05 (obtaining a single average) and the average of the last 4 weeks, for the same day of the week of 22/05 only for the selected states (obtaining a single average).

The final result should be the presentation of a table like:

Date Variation
22/05/2024 X
15/05/2024 Y
08/05/2024 Z
01/05/2024 H
24/05/2024 J

I tried everything, but I couldn't make it work at all. It seems that my filter to calculate the average of the last 4 weeks does not take the State filter into consideration.


Solution

  • You can create these below 3 measures and the difference of these two measures is the value you are looking for-

    average_this_week = 
    
    var this_row_date = min(your_table_name[Date])
    
    
    RETURN 
    CALCULATE(
        AVERAGE(your_table_name[Measurement]),
        FILTER(
            ALL(your_table_name),
            your_table_name[Date] = this_row_date
        )
    )
    
    average_last_4_weeks = 
    
    var this_row_date = min(your_table_name[Date])
    var previous_1_week_date = this_row_date - 7
    var previous_2_week_date = this_row_date - 14
    var previous_3_week_date = this_row_date - 21
    var previous_4_week_date = this_row_date - 28
    
    
    RETURN 
    CALCULATE(
        AVERAGE(your_table_name[Measurement]),
        FILTER(
            ALL(your_table_name),
            your_table_name[Date] = previous_1_week_date
              || your_table_name[Date] = previous_2_week_date
              || your_table_name[Date] = previous_3_week_date
              || your_table_name[Date] = previous_4_week_date
        )
    )
    
    variation =  [average_this_week] - [average_last_4_weeks]
    

    The above measures will response to your state slicer as well. Here is the sample output from your data-

    enter image description here