Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello community,
I have a table with readings about energy consumption for different vehicles. My end goal is to be able to calculate the deviation from their "rolling 12M energy consumption" versus the theoretical energy consumption.
My problem is that I have historical data and don't know how to select the last value for the rolling 12M based on the last reading date for every vehicle.
What I did is create a measure to get the last date:
1 | 2023-02-23 00:00 | 1.3 | 1,2663 | -3% |
2 | 2023-02-23 00:00 | 1.3 | 1,2605 | -3% |
3 | 2023-02-07 00:00 | 1.3 | 1,1984 | -8% |
4 | 2023-02-23 00:00 | 1.3 | 1,1311 | -13% |
5 | 2023-02-23 00:00 | 1.3 | 1,1736 | -10% |
6 | 2023-02-23 00:00 | 1.3 | 1,2098 | -7% |
7 | 2023-02-23 00:00 | 1.3 | 1,179 | -9% |
8 | 2023-02-23 00:00 | 1.3 | 1,1714 | -10% |
9 | 2023-02-23 00:00 | 1.3 | 1,1962 | -8% |
10 | 2023-02-23 00:00 | 1.3 | 1,2028 | -7% |
11 | 2023-02-23 00:00 | 1.3 | 1,2676 | -2% |
12 | 2023-02-16 00:00 | 1.3 | 1,1821 | -9% |
13 | 2023-02-23 00:00 | 1.3 | 1,1854 | -9% |
14 | 2023-02-23 00:00 | 1.26 | 1,4797 | 17% |
15 | 2023-02-22 00:00 | 1.26 | 1,3068 | 4% |
16 | 2023-02-23 00:00 | 1.26 | 1,1672 | -7% |
17 | 2023-02-22 00:00 | 1.26 | 1,2213 | -3% |
18 | 2023-02-15 00:00 | 1.26 | 1,3458 | 7% |
19 | 2023-02-23 00:00 | 1.26 | 1,2974 | 3% |
20 | 2023-02-23 00:00 | 1.1 | 0,9391 | -15% |
That said, how could I count the vehicles based on the following rule and the last date?
Energy Deviation > 10% Red label
Evergy Dev >= -10% or <= 10% Green Label
Energy Dev <-10% Yello Label
I assume since I have been working with measures, I have to continue using them, but not sure how to count the vehicles.
Thanks in advance for any help!
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |