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

[René]

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é

[FreemanZ]

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:

[René]

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é