Hi everyone,
I thought this problem was easier to solve, but for some reason, I can't get the exact expected results and the solutions on the forum aren't exactly applicable.
Here is my main table :
| ID | PERIOD | DATE | STATUS |
| 1 | 2024-02-01 | 2024-02-01 | A |
| 1 | 2024-06-01 | 2024-06-20 | A |
| 2 | 2024-04-01 | 2024-04-14 | B |
| 2 | 2024-05-01 | 2024-05-12 | A |
| 2 | 2024-06-01 | 2024-06-10 | A |
| 2 | 2024-06-01 | 2024-06-18 | B |
| 3 | 2024-09-01 | 2024-09-03 | B |
| 3 | 2024-09-01 | 2024-09-07 | A |
| 4 | 2024-09-01 | 2024-09-28 | B |
| 5 | 2024-03-01 | 2024-03-11 | B |
| 5 | 2024-05-01 | 2024-05-16 | A |
| 5 | 2024-07-01 | 2024-07-21 | B |
| 6 | 2024-08-01 | 2024-08-08 | B |
| 6 | 2024-11-01 | 2024-11-17 | A |
| 7 | 2024-02-01 | 2024-02-28 | B |
| 7 | 2024-05-01 | 2024-05-05 | B |
| 7 | 2024-06-01 | 2024-06-21 | B |
| 7 | 2024-10-01 | 2024-10-27 | A |
| 8 | 2024-03-01 | 2024-03-15 | B |
| 8 | 2024-03-01 | 2024-03-22 | A |
| 9 | 2024-10-01 | 2024-10-22 | B |
| 9 | 2024-11-01 | 2024-11-09 | B |
| 8 | 2024-07-01 | 2024-07-25 | B |
| 10 | 2024-04-01 | 2024-04-07 | A |
| 10 | 2024-11-01 | 2024-11-14 | B |
| 10 | 2024-07-01 | 2024-07-26 | A |
With DAX, I want to count the number of unique IDs (based on the last status) per period (cumulatively) to create a graph showing the status changes.
Here are the expected results:
| PERIODE | STATUT A | STATUT B |
| 2024-02-01 | 1 | 1 |
| 2024-03-01 | 2 | 2 |
| 2024-04-01 | 3 | 3 |
| 2024-05-01 | 5 | 1 |
| 2024-06-01 | 4 | 2 |
| 2024-07-01 | 2 | 4 |
| 2024-08-01 | 2 | 5 |
| 2024-09-01 | 3 | 6 |
| 2024-10-01 | 4 | 6 |
| 2024-11-01 | 4 | 6 |
For example, Period 2024-04-01 has
3 "A" Status (ID: 1, 8, 10) and
3 "B" Status (ID: 2, 5, 7).
I tried to create a first measure that identifies the lastest_status for the appropriate period (cumulatively) but the measure always identifies the last status (for period 2024-04-01, it would falsely classify ID 8 with a "B" Status). Note that it may not be the right way to start.
Thank you very much for any help or advice.
Best Regards,
Eva
