Get value from last year - but apply filter contex...

rfritz · ‎04-01-2022

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:

Create calculated columns
make a measure that has the Table as variable, gets all the records from first to last year, and returns the number of rows. This seems like the way to go on, but I can't figure out how to go on.

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.

rfritz · ‎04-05-2022

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

creating a measure that calculates last year's revenue
and filtering only the years between two explicitly given dates (i.e. passed to the function as an integer).

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