Display and count current status based on last change

[Anonymous]

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).

The PBIX is attached here.

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!

[v-juanli-msft]

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.

[Anonymous]

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

[Anonymous]

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.

[DataInsights]

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.