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.
Hello Helpers,
I'm trying to calculate the Average Annual Growth Rate (AAGR) for each of our customers, but can't get the filter context applied.
I have the following data:
Company | Year | Revenue |
A | 2016 | 350 |
A | 2017 | 390 |
A | 2018 | 402 |
A | 2019 | 489 |
B | 2016 | 1000 |
B | 2017 | 1200 |
B | 2018 | 900 |
B | 2019 | 950 |
C | 2016 | 350 |
C | 2017 | 333 |
C | 2018 | 311 |
C | 2019 | 300 |
C | 2020 | 320 |
D | 2016 | 500 |
D | 2017 | 750 |
D | 2018 | 375 |
To calculate each year's change rate, I get the previous year's revenue value for each year from which I can then calculate the each year's change rate and finally the AAGR for each company as a separate measure.
Company | Year | Revenue | Revenue from Previous Year | Yearly Growth Rate |
A | 2016 | 350 | ||
A | 2017 | 390 | 350 | 11% |
A | 2018 | 402 | 390 | 3% |
A | 2019 | 489 | 402 | 22% |
B | 2016 | 1000 | ||
B | 2017 | 1200 | 1000 | 20% |
B | 2018 | 900 | 1200 | -25% |
B | 2019 | 950 | 900 | 6% |
C | 2016 | 350 | ||
C | 2017 | 333 | 350 | -5% |
C | 2018 | 311 | 333 | -7% |
C | 2019 | 300 | 311 | -4% |
C | 2020 | 320 | 300 | 7% |
D | 2016 | 500 | ||
D | 2017 | 750 | 500 | 50% |
D | 2018 | 375 | 750 | -50% |
I can get these values as calculated columns, but as soon as I apply a filter, this isn't working any more: calculated columns, once calculated, never change, I've learned. So if I filter out 2016, for example (access the example worksheet here), no company should have any value in growth rate for 2017 (since there is nothing to grow from). And yet, the value stays the same and thus get calculated, too.
So I believe I need to calculate these auxiliary values as a table variable in a measure – and return the AAGR for each company. But from here on, I'm stuck and need help.
There's one additional consideration that I have to take into account at some point: Since I want to calculate the Average Annual Growth Rate, I can't just filter out any years in the middle (otherwise, it would be an Average Change Rate for the years selected). So filter context should apply to top and bottom years, but not the ones in between. But I think I already found out how to do this, so this is really not my focus.
What I have done in the worksheet is this:
Thus, any help is appreciated, thank you!
Raphael
If I can do anything to make my problem more clear, don't hesitate to tell me.
I might have found some part of how to do this:
The measure below returns each previous year's revenue for each company for a timeframe between two years. So I succeeded in
Last Years Revenue =
VAR firstSelectedYear = FIRSTDATE('Company data'[Year].[Date])
VAR lastSelectedYear = LASTDATE('Company data'[Year].[Date])
VAR LastYearsRevenue = CALCULATE(
SUM('Company data'[Revenue]),
SAMEPERIODLASTYEAR('Company data'[Year].[Date]),
// This works when it's an integer, but not when it's a variable:
YEAR('Company data'[Year]) >= 2017,
YEAR('Company data'[Year]) < 2019
// This does not work:
// YEAR('Company data'[Year]) >= YEAR(firstSelectedYear),
// YEAR('Company data'[Year]) < YEAR(lastSelectedYear)
)
RETURN LastYearsRevenue
The result:
But this measure only works with an explicitly given integer – not the actual dates. So what I need to achieve now is to integrate the first and last selected years as variables adjusting to the slicer in the report.
So it feels like having come half way here, at best. Can anyone give me some tips on
Hi @rfritz ,
Do you mean if you unselect 2018 in the year slicer, the Previous Year's Revenue should be 390(Year2017), but not 402(Year2018)? As well as the AAGR?
Best Regards,
Community Support Team _ kalyj
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 |
---|---|
47 | |
24 | |
20 | |
15 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |