Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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 still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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 still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@Anonymous , 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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |