Percent Difference of same column but different year

[Anonymous]

Hi all,

I want to show to perecent differnce of year 2020 and 2021 for column A_ Total CO2. Does someone know the formula for this?

Thanks in advance!

[v-stephen-msft · Accepted solution]

Hi @Anonymous ,

You are trying to calculate the percentage change between the current year and the previous year, right?

From your screenshot, I guess you should also have a column similar to my Group column below that groups the years.

Try this measure

Percentage =
VAR _preyear =
    CALCULATE (
        SUM ( 'Table'[A_Total CO2] ),
        FILTER (
            ALL ( 'Table' ),
            [Group] = MAX ( 'Table'[Group] )
                && [Year]
                    = MAX ( 'Table'[Year] ) - 1
        )
    )
RETURN
    DIVIDE ( SUM ( 'Table'[A_Total CO2] ) - _preyear, _preyear )

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , with a separate date or year table you can have measure like example

//Only year vs Year, not a level below

This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

//exact year

This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=2020))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=2021))

diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

[Rygaard]

OR you could use "Sameperiodlastyear"

so

CO2 = Calculate(sum('Table'[Qty])

C02, LY = calculate(CO2),SAMEPERIODLASTYEAR('Calendertable'[DateValue]))

This has the added value that if you set a filter  that exclude dates that is after today, it will only compare for the same date on last year, so you can compare incomplete years directly

EG.

you set filter for no dates after to day

then you get 2022-01-01 .. 2022-01-25   compared to 2021-01-01..2021-01-25 

and if you remove the no dates after today, then you get the full 2021 ... we use this alot