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.
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-