How to get a correct count when statuses change

[dostdahl]

Hello,

I'm running into an issue achieving a correct status count for a unique account number. Here is the scenario:

We are tracking the status that an account is currently in. The statuses can be updated and the new status dates are tracked as well. The 4 statuses are Research, Cultivation, Solicitation, and Stewardship. For whatever the current status that an account is in, I've created 4 calculated columns that I use to sum up for a total.

The calcuated column DAX is currently:

Research Count= IF(Table[Status] = "Research", 1, 0).

This is used for each status and then I tally up the 1's to get a count. It works fine until an account changes a status, which looks something like this:

Unique ID	Status	Date From	Previous Status	Previous Date	Research Count	Cultivation Count
1	Research	3/28/2022			1	0
1	Cultivation	3/30/2022	Research	3/28/2022	0	1

The new current status for the unique ID has a 1 in the column, but the Research count also keeps the 1 which is throwing off my actual count. 

My desired outcome would be able to account for these status changes and only show a 1 in the status column for the current status and show a 0 for the previous/other statuses for each unique ID.

Unique ID	Status	Date From	Previous Status	Previous Date	Research Count	Cultivation Count
1	Research	3/28/2022			0	0
1	Cultivation	3/30/2022	Research	3/28/2022	0	1

I've tried a few differnet ways in DAX, but am still learning. Thanks in advance for any advice, let me know if I can provide any additional details!

-Dereck

[Anonymous]

Hi @dostdahl 

From your DAX you can see , if Status = "Research" then return 1 , so it return 1 in Research Count . From the data and formulas you have provided so far, there is nothing wrong with this .

Could you provide your pbix file or more detailed data or formulas to illustrate your problem ?

Best Regards,
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.

[dostdahl · Accepted solution]

Hi @Anonymous - Thank you for your response. After looking at it some more, I was able to find a workable solution. As you stated above, the calculated columns were returning a 1 for what ever Status was in the Status column, for each Unique ID. But when the status changes, I needed to have the old status show a 0, with the new status showing 1.

The solution I found was to create a calculated column to show the most recent date for a status:

Newest Status Date =

MAXX (

FILTER (

ALL ( Table ),

Table[Unique ID] = EARLIER ( Table[Unique ID] )

),

Table[Date From]

)

And then adjusted the Research Count and Cultivation Count column DAX to: 

Research Count =

IF (

Table[Status] = "Research"

&& Table[Date From] = Table[Newest Status Date],

1,

0

)

This allows the table to show 1 current status count when there is a change. 

Unique ID	Status	Date From	Previous Status	Previous Date	Research Count	Cultivation Count	Newest Status Date
1	Research	3/28/2022			0	0	3/30/2022
1	Cultivation	3/30/2022	Research	3/28/2022	0	1	3/30/2022

Thanks again for your help.

-Dereck