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



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s || 50+Power Query List Functions : Learn Power Query List
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

@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
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors