Forum Discussion
Helper VSelect las value from a measure
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:
And that works well to create a table where I have vehicle_id, last date (measure), energy consumption, rolling 12M energy consumption and Energy Deviation like:
| 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!