Follow status in a selected month and status change in the following months

[jbrooi]

Hi guys, can't seem to find the solution for this scenario. 

I want to filter a specific month, let's say Feb 2023. I'd like to see how many people have a status "Nieuw" in that month, and from that group of people how many have a status "Trainee" in that or any following month. I want to display that in a timeline, with a value for "Nieuw" in Feb 2023 and values for "Trainee" in Feb, March, April and so on. 

How to calculate the measures for this?

I'm stuck on specifying the "Nieuw" group in Feb 2023 in combination with showing the "Trainee" statuses in the other months. 

Thanks! 

I have a change table for the people and a connected Date table. The change table is like this:

KandidaatID	Status	statusID	Ingangsdatum
75041	Nieuw	75	1-2-2023 00:00
75042	Nieuw	75	1-2-2023 00:00
75043	Nieuw	75	1-2-2023 00:00
75044	Nieuw	75	1-2-2023 00:00
75045	Nieuw	75	1-2-2023 00:00
75046	Nieuw	75	1-2-2023 00:00
75046	Trainee	9119	15-2-2023 00:00
75047	Nieuw	75	1-2-2023 00:00
75048	Nieuw	75	2-2-2023 00:00
75049	Nieuw	75	2-2-2023 00:00
75050	Nieuw	75	2-2-2023 00:00
75051	Nieuw	75	3-2-2023 00:00
75051	Trainee	9119	15-2-2023 00:00
75052	Nieuw	75	3-2-2023 00:00
75053	Nieuw	75	3-2-2023 00:00
75054	Nieuw	75	3-2-2023 00:00
75055	Nieuw	75	3-2-2023 00:00
75056	Nieuw	75	3-2-2023 00:00
75057	Nieuw	75	5-2-2023 00:00
75058	Nieuw	75	5-2-2023 00:00
75059	Nieuw	75	5-2-2023 00:00
75060	Nieuw	75	6-2-2023 00:00
75061	Nieuw	75	6-2-2023 00:00
75062	Nieuw	75	6-2-2023 00:00
75063	Nieuw	75	6-2-2023 00:00
75064	Nieuw	75	6-2-2023 00:00
75064	Trainee	9119	14-2-2023 00:00
75065	Nieuw	75	6-2-2023 00:00
75066	Nieuw	75	6-2-2023 00:00
75067	Nieuw	75	6-2-2023 00:00
75068	Nieuw	75	6-2-2023 00:00
75068	Trainee	9119	11-4-2023 00:00
75069	Nieuw	75	6-2-2023 00:00
75070	Nieuw	75	6-2-2023 00:00
75071	Nieuw	75	7-2-2023 00:00
75072	Nieuw	75	7-2-2023 00:00
75073	Nieuw	75	7-2-2023 00:00
75074	Nieuw	75	7-2-2023 00:00
75074	Trainee	9119	13-2-2023 00:00
75075	Nieuw	75	7-2-2023 00:00
75076	Nieuw	75	7-2-2023 00:00
75077	Nieuw	75	7-2-2023 00:00
75078	Nieuw	75	7-2-2023 00:00
75078	Trainee	9119	9-3-2023 00:00
75079	Nieuw	75	7-2-2023 00:00
75079	Trainee	9119	15-2-2023 00:00
75080	Nieuw	75	7-2-2023 00:00
75081	Nieuw	75	7-2-2023 00:00
75082	Nieuw	75	8-2-2023 00:00
75083	Nieuw	75	8-2-2023 00:00
75084	Nieuw	75	8-2-2023 00:00
75085	Nieuw	75	8-2-2023 00:00
75086	Nieuw	75	8-2-2023 00:00
75087	Nieuw	75	8-2-2023 00:00
75088	Nieuw	75	8-2-2023 00:00
75088	Trainee	9119	21-2-2023 00:00
75089	Nieuw	75	8-2-2023 00:00
75090	Nieuw	75	8-2-2023 00:00
75090	Trainee	9119	13-2-2023 00:00
75091	Nieuw	75	8-2-2023 00:00
75092	Nieuw	75	8-2-2023 00:00
75093	Nieuw	75	8-2-2023 00:00
75094	Nieuw	75	9-2-2023 00:00
75095	Nieuw	75	9-2-2023 00:00
75096	Nieuw	75	9-2-2023 00:00
75097	Nieuw	75	10-2-2023 00:00
75098	Nieuw	75	10-2-2023 00:00
75098	Trainee	9119	15-2-2023 00:00

[jbrooi · Accepted solution]

Ah found it, didn't apply the ALL function in the right way. This gives me the right result:

Trainees from selected month = 

Trainees uit geselecteerde maand =

VAR NieuweKandidaten =

    CALCULATETABLE(

        VALUES('EmployeeChange'[KandidaatID]),

        'EmployeeChange'[Status] = "Nieuw"

    )

RETURN

    CALCULATE(

        COUNTROWS('EmployeeChange'),

        ALL(EmployeeChange),

        'EmployeeChange'[Status] = "Trainee",

        'EmployeeChange'[KandidaatID] IN NieuweKandidaten

    )

[amitchandak]

@jbrooi , Create a disconnected date table

Measure =
Var _sel = Eomonth(maxx(allselected(Date), Date[Date]), 0)
return
countrow(filter(Table, Table[Status]="Nieuw" && eomonth(Table[Ingangsdatum],0)= _max)) + countrow(filter(Table, Table[Status]="Trainee" && eomonth(Table[Ingangsdatum],0)> _max))

Need of an Independent/disconnected Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI