Count of Users who's Status changes

[Anonymous]

Hi, so I have a table that shows a User ID and their Status.

The three types of Status are: Started, Ended and Archived. How would I be able to have a count of users on my report that shows when a User's Status changed from Started to Ended and then another count from Ended to Archived.

The Status will be changed in the database so it is still the same column, it just changes from Started to Finished to Archived at different times.

Also, how would I be able to reset the count? So after I view the count of how many people changed status, how would I reset it so those people will then be ignored from there on.

Thank you.

[Anonymous · Accepted solution]

Hi @Anonymous 

Original date :

Create a measure to count the ID whose Status is “Archived” .

Ended to Archived = CALCULATE(COUNT('Table'[User ID]),FILTER('Table','Table'[Status]="Archived"))

Create a measure to count the ID whose Status is “Ended” and without “Archived” .

Started to Ended =
VAR _SUMMARIZE = SUMMARIZE(FILTER('Table',AND('Table'[Status] = "Ended",'Table'[Date]=CALCULATE(MAX('Table'[Date]),FILTER('Table','Table'[User ID]=EARLIER('Table'[User ID]))))),'Table'[User ID],'Table'[Date],'Table'[Status])
RETURN
COUNTAX(_SUMMARIZE,[User ID])

The final result is as shown :

Measure is dynamic , so if the data in your data source changes , the measures will also change .

I have attached my pbix file , you can refer to it .

Best Regard

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.