Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a bunch of entities, and new entities are added regularly. They change status over time. I need to see in any given week how many entities are in each status (also stated as, what status each entity is in at the end of each week).
Given this simplified data:
EntityID | Date | Status |
524991 | 4/5/2020 | Not Started |
524991 | 4/20/2020 | Ready |
524991 | 4/20/2020 | In Progress |
550937 | 5/12/2020 | Not Started |
What I want to see is:
Week Ending | Not Started | Ready | In progress | Total |
4/10 | 1 | 0 | 0 | 1 |
4/17 | 1 | 0 | 0 | 1 |
4/24 | 0 | 0 | 1 | 1 |
5/1 | 0 | 0 | 1 | 1 |
5/8 | 0 | 0 | 1 | 1 |
5/15 | 1 | 0 | 1 | 2 |
5/22 | 1 | 0 | 1 | 2 |
The PBIX has more info.
I feel like this should be a fairly straightforward calculation, but nothing I'm doing is working. Thanks much for your help!
Hi @Anonymous
I create measures to display "not start" and "in progress', but don't know the logic to get "ready".
Please let me know that.
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for responding. Were you able to open the attached PBIX? It has the test dataset. Here's the link again in OneDrive in case Google Drive didn't open.
I have entities with unique IDs. Each entity goes through status changes over time. Each status change is timestamped and has an event ID. I have a WeekEnd column. Right now, the WeekEnd column only shows me which events *changed* in that week. I need to see the status of all entities based on whatever their last status was.
Maybe this helps illustrate better, using the dataset in the PBIX. If I were to record statuses at the end of each week, it might look like this (subset of data; highlighted rows show where the status has changed as examples).
EntityID | 5/31/2020 | 5/24/2020 | 5/17/2020 | 5/10/2020 | 5/3/2020 | 4/26/2020 | 4/19/2020 | 4/12/2020 | 4/5/2020 | 3/29/2020 |
685656 | Not Started | Not Started | ||||||||
687061 | Approved | Approved | In Progress | |||||||
688551 | Not Started | Not Started | Not Started | Not Started | ||||||
709896 | Not Started | Not Started | Not Started | Not Started | Not Started | |||||
726720 | Not Started | Not Started | Not Started | Not Started | Not Started | |||||
771374 | Not Started | Not Started | Not Started | Not Started | Not Started | Not Started | ||||
771450 | Not Started | Not Started | Not Started | Not Started | Not Started | Not Started | ||||
788864 | Not Started | Not Started | Not Started | Not Started | Not Started | Not Started | ||||
803913 | Not Started | Not Started | Not Started | Not Started | Not Started | Not Started | Not Started | |||
808265 | Not Started | Not Started | Not Started | Not Started | Not Started | Not Started | Not Started | |||
808890 | Not Started | Not Started | Not Started | Not Started | Not Started | Not Started | Not Started | Not Started | ||
819233 | Not Started | Not Started | Not Started | Not Started | Not Started | Not Started | Not Started | Not Started | ||
852584 | In Progress | In Progress | In Progress | In Progress | In Progress | In Progress | In Progress | In Progress | ||
865123 | Not Started | Not Started | Not Started | Not Started | Not Started | Not Started | Not Started | Not Started | Not Started | |
879061 | Not Started | Not Started | Not Started | Not Started | Not Started | Not Started | Not Started | Not Started | Not Started | |
902490 | Not Started | Not Started | Not Started | Not Started | Not Started | Not Started | Not Started | Not Started | Approved | Approved |
958735 | Not Started | Not Started | Not Started | Not Started | Not Started | Not Started | Not Started | Not Started | Not Started | Not Started |
Then if I count all of the entities each week in their respective week-ending status, it should look like this:
WeekEnd | Not Started | Ready | In Progress | Approved | Total |
5/31/2020 | 86 | 1 | 3 | 9 | 99 |
5/24/2020 | 79 | 1 | 3 | 7 | 90 |
5/17/2020 | 62 | 1 | 4 | 5 | 72 |
5/10/2020 | 56 | 0 | 3 | 5 | 64 |
5/3/2020 | 53 | 0 | 3 | 5 | 61 |
4/26/2020 | 42 | 0 | 2 | 4 | 48 |
4/19/2020 | 38 | 0 | 2 | 2 | 42 |
4/12/2020 | 32 | 0 | 2 | 2 | 36 |
4/5/2020 | 16 | 0 | 1 | 3 | 20 |
3/29/2020 | 15 | 0 | 0 | 1 | 16 |
1/19/2020 | 8 | 0 | 0 | 0 | 8 |
1/12/2020 | 7 | 0 | 0 | 0 | 7 |
10/13/2019 | 6 | 0 | 0 | 0 | 6 |
10/6/2019 | 5 | 0 | 0 | 0 | 5 |
9/29/2019 | 4 | 0 | 0 | 0 | 4 |
7/21/2019 | 3 | 0 | 0 | 0 | 3 |
5/5/2019 | 2 | 0 | 0 | 0 | 2 |
4/28/2019 | 1 | 0 | 0 | 0 | 1 |
Maybe this will help - this is from my live data. I want to see that the first one was In Progress on Apr 17, May 22, and May 29 before it switched to Pending Approval. I want to see the second one is In Progress for Jun 5. For any given week, I want to see whatever the last change was. I've tried LASTDATE and LASTNONBLANK and nothing is working.
Hello,
1. Add a column Week Ending to your Date table. DAX: [Date] - WEEKDAY([Date],2) + 7 (adjust this for Saturday vs. Sunday end date)
2. In the matrix, add Week Ending to the rows. (your data table should be joined to the Date table)
3. Create a measure for each status. DAX: Not Started = CALCULATE ( COUNT ( Entity[EntityID] ), Entity[Status] = "Not Started" )
4. Drag the measure for each status into the values.
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |