Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
This totally worked! Thank you immensely for taking the time to recreate this in pbi and sharing it with me! You're a savior!
@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.
Thank you for your help and the resources!
User | Count |
---|---|
98 | |
90 | |
83 | |
70 | |
67 |
User | Count |
---|---|
115 | |
104 | |
101 | |
72 | |
64 |