topic Calculate 4 years average but only when a year has a value in DAX Commands and Tips

Calculate 4 years average but only when a year has a value

René — Mon, 08 May 2023 11:27:31 GMT

Hi,

I am currently struggeling with the following.

One of our PI's is that we monitor the growth of customers. Therefore we have the following condition: we call a customer a "growth customer" if he/she: grows more than 200% (ytd) then the average of the previous 4 years (only calculating with the years with turnover) and has a minimum turnover (ytd) of 12k€.

In Excel I have managed this, but I really love to automate this in Power BI.

In order to isolate the previous years, I have found the following code working:

Turover Year -1 = CALCULATE ( [Turnover], FILTER ( 'Calendar', YEAR ( 'Calendar'[Date] ) = YEAR ( MAX ( 'Calendar'[Date] ) ) - 1 ) )

So I have 1 measure for per previous year (Year -1, -2 -3, and -4)

Sample data:

Customer	2019	2020	2021	2022	2023	Years with TO	Avg	% increase	Growth Customer
A		€ 3.964	€ 541	€ 2.448	€ 75.741	3	€ 2.318	3268%	Yes
B				€ 1.918	€ 19.078	1	€ 1.918	995%	Yes
C	€ 24.771	€ 30.329	€ 44.712	€ 33.268	€ 67.470	4	€ 33.270	203%	Yes
D	€ 18.940	€ 16.222	€ 10.231	€ 15.071	€ 42.176	4	€ 15.116	279%	Yes
E	€ 14.950	€ 18.000	€ 12.000	€ 20.000	€ 23.000	4	€ 16.238	142%	No
F			€ 5.000	€ 6.500	€ 11.500	€ 2	€ 5.750	200%	No (because <12k€)

So far, I couldn't figure out how the get this outcome (yes/no) per customer.

Maybe someone can help me out with this?

Many thanks in advance!

René

Re: Calculate 4 years average but only when a year has a value

FreemanZ — Mon, 08 May 2023 13:24:43 GMT

hi René

try to:

1) unpivot your dataset to something like:

about unpivot: https://learn.microsoft.com/en-us/power-query/unpivot-column

2) plot a table visual with the customer column and a measure like:

Growth Customer = VAR _avg = AVERAGEX( FILTER( data, data[Year]<2023 ), data[Turnover] ) VAR _2023= MAXX( FILTER( data, data[Year]=2023 ), data[Turnover] ) RETURN IF( _2023>_avg*2&&_2023>12, "Yes", "No" )

it worked like:

Re: Calculate 4 years average but only when a year has a value

René — Tue, 09 May 2023 08:17:45 GMT

Hi FreemanZ ,

Many thanks for your swift response. Seeing your answer, I realized that I didn't give you all required info. The sample data I gave was the output I'd like to achieve. The actual dataset is build up with thousands and thousands of detailed invoicelines. So I somehow first need to calculate the sum per annum.

Using your code I end up with an average per line, not per year (my bad).

This is how my dataset is designed:

Maybe you can help my again with this?

Best regards,

René