Need to calculate Year-over-Year Growth for "year" data

[laser_beam]

Hello,

I need to calculate year-over-year growth for some of my data (I only have "year" data (from 2018-2021)) and I read that I first have to add a Calendar table. I have tried for a week to add it and link it to the "Year" column for one of my tables but when I plot it (see top screenshot) it doesn't plot it correctly. In fact, the secondary axis values are incorrect (it has them as 20-30M but the highest the data goes is 8M). The bottom screenshot is how it's supposed to look when I don't use the "Years table". I haven't even done the year-over-year formula yet until I know that the Year table will work as it's supposed to. Can anyone please help me create a correct "Year" table and year-over-year formula? I have also included a screenshot of my data columns in its entirety for reference. 

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

All measures are in the attached pbix file.

Passengers YoY Growth: =
VAR _currentyearpassenger = [Passengers total:]
VAR _currentyear =
MAX ( 'Year'[Year] )
VAR _previousyearpassenger =
CALCULATE ( [Passengers total:], 'Year'[Year] = _currentyear - 1 )
VAR _YoYGrowth =
DIVIDE (
_currentyearpassenger - _previousyearpassenger,
_previousyearpassenger
)
RETURN
IF (
HASONEVALUE ( 'Year'[Year] ),
IF (
NOT ISBLANK ( _currentyearpassenger ) && NOT ISBLANK ( _previousyearpassenger ),
_YoYGrowth
)
)

[laser_beam]

This totally worked! Thank you immensely for taking the time to recreate this in pbi and sharing it with me! You're a savior!

[amitchandak]

@laser_beam , At what level you have data ?

If only at year level

You can create date

Date = date([Year],1,1)

Using date table and time intelligence

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

Only using year table(A separate Year table of date tbale)

//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))

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

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[laser_beam]

Thank you for your help and the resources!