Count ID by first year of occurance

[Richard77]

Hi all,

I think I am very close to a solution but I keep on miss hitting.

My table looks as follows:

Client ID	Start date	End date	Project year
1	01-01-2021	31-12-2021	2021
1	01-01-2022	31-12-2023	2022
1	01-01-2022	31-12-2023	2023

So each client has multipe project years based on the start and end date. 

Next to this table a date table is linked with relations the start and end date (currently both inactive).

What I need is the 'growth in clients per year'.

In the case above it is relatively simple: in 2021, being the first year client_id 1 is involved, we have 1 new clients.

How do I create a measure or calculated column that I can put in a bar chart where the x-axis is a year (pulled from the date table) and the y-axis is the number of new clients for that specific year). 

If client number 2 starts in 2023 the bar for 2021 would be 1, for 2022 0 and for 2023 1.

Very curious what measure solves my hurdle here. Thank you very much already!

[tamerj1]

Hi @Richard77 
Please use

Number of Clients =
VAR CurrentYear =
    YEAR ( MAX ( 'Date'[Date] ) )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Projects[Client ID] ),
        Projects[Project Year] = CurrentYear
    )

[Richard77]

Dear @tamerj1 ,

thank you for your quick reply! It does not completely work, as some clients appear in both 2021 and 2022. An example is a client that has a project from 01-01-2021 until 31-12-2021 AND another project from 01-01-2022 until 31-12-2022. In the bar chart (using your calculation for the y-axis and the date table year for the x-axis, this organisation shows in both 2021 and 2022 where it was expected ONLY in 2021 as this was the first year the client has been involved. Does this give some more context?

Cheers!

[Richard77 · Accepted solution]

Hello all,

I've now found this as a solution and it fits my needs:

Min = VAR minValue = calculate(MIN('Calculated table'[Project year]), ALLEXCEPT('Calculated table', 'Calculated table'[executor_id])) return IF('Calculated table'[Project year] = minValue, "MIN")