cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
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).

The PBIX is attached here.

Given this simplified data:

EntityIDDateStatus
5249914/5/2020Not Started
5249914/20/2020Ready
5249914/20/2020In Progress
5509375/12/2020Not Started

What I want to see is:

Week EndingNot StartedReadyIn progressTotal
4/101001
4/171001
4/240011
5/10011
5/80011
5/151012
5/221012

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!

4 REPLIES 4
Highlighted
Solution Sage
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
Community Support

Hi @GSMITH 

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

Please let me know that.

Capture6.JPG

 

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

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

 
EntityID5/31/20205/24/20205/17/20205/10/20205/3/20204/26/20204/19/20204/12/20204/5/20203/29/2020
685656Not StartedNot Started        
687061ApprovedApprovedIn Progress       
688551Not StartedNot StartedNot StartedNot Started      
709896Not StartedNot StartedNot StartedNot StartedNot Started     
726720Not StartedNot StartedNot StartedNot StartedNot Started     
771374Not StartedNot StartedNot StartedNot StartedNot StartedNot Started    
771450Not StartedNot StartedNot StartedNot StartedNot StartedNot Started    
788864Not StartedNot StartedNot StartedNot StartedNot StartedNot Started    
803913Not StartedNot StartedNot StartedNot StartedNot StartedNot StartedNot Started   
808265Not StartedNot StartedNot StartedNot StartedNot StartedNot StartedNot Started   
808890Not StartedNot StartedNot StartedNot StartedNot StartedNot StartedNot StartedNot Started  
819233Not StartedNot StartedNot StartedNot StartedNot StartedNot StartedNot StartedNot Started  
852584In ProgressIn ProgressIn ProgressIn ProgressIn ProgressIn ProgressIn ProgressIn Progress  
865123Not StartedNot StartedNot StartedNot StartedNot StartedNot StartedNot StartedNot StartedNot Started 
879061Not StartedNot StartedNot StartedNot StartedNot StartedNot StartedNot StartedNot StartedNot Started 
902490Not StartedNot StartedNot StartedNot StartedNot StartedNot StartedNot StartedNot StartedApprovedApproved
958735Not StartedNot StartedNot StartedNot StartedNot StartedNot StartedNot StartedNot StartedNot StartedNot Started

Then if I count all of the entities each week in their respective week-ending status, it should look like this:

WeekEndNot StartedReadyIn ProgressApprovedTotal
5/31/20208613999
5/24/20207913790
5/17/20206214572
5/10/20205603564
5/3/20205303561
4/26/20204202448
4/19/20203802242
4/12/20203202236
4/5/20201601320
3/29/20201500116
1/19/202080008
1/12/202070007
10/13/201960006
10/6/201950005
9/29/201940004
7/21/201930003
5/5/201920002
4/28/201910001

 

Highlighted

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.

 

GSMITH_1-1595334534008.png

 

 

 

 

 

 

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors