YOY - Year wise comparison

[Anonymous]

I have Five years data in the Excel . data has been loaded in to Power bi . i want find the percentage of YOY comparison.

year             vaule         Month 

2021             450       Jan -21

2021             450       Feb -21

2022             350       Jan -22

2022             250       Feb -22

2023             450       Jan -23

2023             150        Feb -23

2024             650        Jan -24

2024             150        Feb -24

2025             450        Jan -25 

2025             50          Feb -25

2022-2021 and  2023 - 2022  & 2024 - 2023 , 2025-2024

Formula = (current year - perviousyear)/ previous year

LIke (2022-2021)/2021

Thanks in advance 

[FreemanZ]

hi @Anonymous 

try to plot any visual with year column and a measure like:

YoY% = 
VAR _valuepre = 
    CALCULATE(
        SUM(TableName[Value]),
        TableName[Year] = MAX(TableName[Year])-1
    )
VAR Result =
    DIVIDE(
        SUM(TableName[Value]),
        _valuepre
    )-1

RETURN
    IF(
        _valuepre<>BLANK(),
        Result
    )

it worked like:

p.s. as suggested by joht75, in the long run, you shall be working with dedicated date tables. 

[johnt75]

Set up a proper date table and link it to your fact table. If the month column in your fact table isn't already a full date you need to convert it to be the first day of the month, and you can control how it displays using the format string.

Once you have the date table linked to the fact table, use columns from the date table in your visuals and you can create a measure like

YoY % =
VAR CurrentValue =
    SUM ( 'Table'[Value] )
VAR LastYear =
    CALCULATE ( SUM ( 'Table'[Value] ), DATEADD ( 'Date'[Date], -12, MONTH ) )
RETURN
    DIVIDE ( CurrentValue - LastYear, LastYear )