Select las value from a measure

[Victormar]

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: 

m_LastDate = CALCULATE ( 

    MAX ( FACT_Readings[Date] ) , NOT ( ISBLANK ( FACT_Readings[EGC_P1F2K] ) ) )

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:

Vechile                       Last DateEnergy                     Consumption Theoretical       Energy R12         Energy Dev

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!