How to create a measure that use result from old year selected as baseline to calculate future years

[souzacaleb]

I would like to create a metric that use the total Emission from the Year selected as a baseline to calculate new values for the future years by 2024 to 2030 adding 1% of efficiency

(*) The % of efficiency is a variable that can be changed. for example: 1%, 5%,10% , etc
(**) The emission table has data from 2015 to 2019 in the example I´ve just selected the year 2019 as a baseline, but I should be able to select any other previous year available.

Emission Table

Date	Emission
1/1/2019	557
2/1/2019	432
3/1/2019	1554
4/1/2019	2234
5/1/2019	354
6/1/2019	4464
7/1/2019	446
8/1/2019	958
9/1/2019	3465
10/1/2019	741
11/1/2019	6331
12/1/2019	4566

For example if I select the Year 2019 on the emission table I want to use the emission result from 2019 to calculate new values for  each month by 2024 to 2030 following these rule below

Emission for Jan 2024 = Emission of Jan 2019 - ( (Total Emission in the whole year 2019 /12 ) * 1%)
Emission for Feb 2024= Emission of Feb 2019 - ( (Total Emission in the whole year 2019 /12 ) * 1%) 
...
Emission for Jan 2025 = Emission of Jan 2024 - ( (Total Emission in the whole year 2024 /12 ) * 1%)
Emission for Feb 2025= Emission of Feb 2024 - ( (Total Emission in the whole year 2024 /12 ) * 1%)
...
Emission for Jan 2030 = Emission of Jan 2029 - ( (Total Emission in the whole year 2029 /12 ) * 1%)
Emission for Feb 2030= Emission of Feb 2029 - ( (Total Emission in the whole year 2029 /12 ) * 1%)

[Anonymous · Accepted solution]

HI @souzacaleb,

You can try to use the following measure formula to get the same period last year value as current period baseline:

formula =
//percentage value from new table
VAR _rate =
    MAX ( Selector[Rate] )
VAR currDate =
    MAX ( Table1[Date] )
VAR prevDate =
    DATE ( YEAR ( currDate ) - 1, MONTH ( currDate ), DAY ( currDate ) ) 
//last year same period emission
VAR LYMEmission =
    CALCULATE (
        SUM ( Table1[Emission] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            YEAR ( [Date] ) = YEAR ( prevDate )
                && MONTH ( [Date] ) = MONTH ( prevDate )
        )
    ) 
//last year emission
VAR LYEmission =
    CALCULATE (
        SUM ( Table1[Emission] ),
        FILTER ( ALLSELECTED ( Table1 ), YEAR ( [Date] ) = YEAR ( prevDate ) )
    )
RETURN
    prevEmission - LYEmission / 12 * _rate

Regards,

Xiaoxin Sheng