cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rlee1982
Helper III
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

YearCustomerRevenue
2017A10
2017B20
2017C15
2018A5
2018B10
2019A15
2019B30
2019C10
2019D15

 

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%
2019180%

 

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

1 ACCEPTED SOLUTION
CNENFRNL
Super User
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!

View solution in original post

3 REPLIES 3
CNENFRNL
Super User
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!

amitchandak
Super User
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

@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! 

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.