cancel
Showing results for
Did you mean:
Highlighted
Helper III

## Display and count current status based on last change

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

I feel like this should be a fairly straightforward calculation, but nothing I'm doing is working. Thanks much for your help!

4 REPLIES 4
Highlighted
Solution Sage

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.

Highlighted
Community Support

Hi @GSMITH

I create measures to display "not start" and "in progress', but don't know the logic to get "ready".

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.

Highlighted
Helper III

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

Highlighted
Helper III

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.

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors