year over year change

[Anonymous]

hey every one, i have a bit of a problem that involves calculating year over year difference. so i have a date column, in it it has many dates spanning from 2018 to 2020. then i made a year column base on that date column, so the year column has 3 kind of entries, 2018, 2019, 2020. and then i have a column called amount, it is a numerical column. so i drag these columns into a line and stacked column chart. the axis are the years and the values are the amount. now i want to make a measure that can show the year over year change in the amount for the years, and then put that measure in the line value of the line and stacked column chart. the table it self is named sector_revenue. how can i write this dax?

any help is appreciated, thank you.

[fullcount]

Hi @Anonymous,

I'm sure there are other ways, but I think I would make a separate date table.  You can go from zero to working in just a couple of minutes with this table.  Make a many-to-one relationship between your sector_revenue date column and this new date table date column, with the sector_revenue table on the many side.  Then write the following measures for some YTD versions which will be easy to use with your new date dimensions in any chart:

YTD Revenue = CALCULATE(SUMX(sector_revenue[amount]),DATESYTD(Date_Table[Date]))

LY YTD Revenue = CALCULATE([YTD Revenue],SAMEPERIODLASTYEAR(Date_Table[Date]))

Revenue Y-o-Y Change = [YTD Revenue] - [LY YTD Revenue]

Revenue Y-o-Y % Change = DIVIDE([Revenue Y-o-Y Change],[LY YTD Revenue],0)

Hope that helps,

Ben

[AlexisOlson]

I recommend checking out the patterns here (particularly the year-over-year growth) as a starting point.