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.