cancel
Showing results for
Did you mean:
Helper III

## Calculate Year-over-year change in another way

Dear all

I am trying to calcualte year-over-year change by another way (not using date table).

Year_Filter

 Year 2017 2018 2019

Data

 Year Customer Revenue 2017 A 10 2017 B 20 2017 C 15 2018 A 5 2018 B 10 2019 A 15 2019 B 30 2019 C 10 2019 D 15

I am trying to calculate the year-over-year change rate by the measure below, but it does not work:

YoY_Change_ =

VAR _current_ = calculate(sum(Revenue))

VAR _last_year_ = calculate(sum(Revenue), FILTER(data, data[data] = selectedvalue(Year_Filter[Year]-1)

return

divide(_current_,_last_year)-1

I expect this measure will return:

 YoY 2017 2018 -44% 2019 180%

Can someone kindly point out how I should write the measure? Many thanks!

1 ACCEPTED SOLUTION
Super User

 Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is beyond their comprehension! DAX is simple, but NOT EASY!
3 REPLIES 3
Super User

 Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is beyond their comprehension! DAX is simple, but NOT EASY!
Super User

@rlee1982 , try like

YoY_Change_ =
var _max = maxx(allselected(Year_Filter), Year_Filter[Year])
VAR _current_ = calculate(sum(Revenue))
VAR _last_year_ = calculate(_current_, FILTER(all(Year_Filter), Year_Filter[Year] = _max-1))
return
divide(_current_,_last_year)-1

Helper III

@amitchandakthank you for your reply as well. Your solution works perfectly as well. Sadly I can only select one solution at this moment. Much appreciated!

Announcements