The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
Solved! Go to Solution.
Ah found it, didn't apply the ALL function in the right way. This gives me the right result:
Trainees from selected month =
Ah found it, didn't apply the ALL function in the right way. This gives me the right result:
Trainees from selected month =
@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