Status change and count

[Anonymous]

Hi everyone,

I have a sample table below and I want to be able to count current status based on period:

Name	Referred Date	Accepted Date	Enrollment Date	Exit Date
A	3/4/2020	3/9/2020	3/13/2020	12/1/2020
B	5/5/2020
C	7/7/2020	8/8/2020	9/8/2020
D	9/2/2020	10/3/2020
E	11/8/2020

The status of each name changed when moving from referred date to exit date. For example, if the date of accept, enrol and exit is blank, the status of the name will remain "Referred". If the accepted date is not blank but there is no enrol and exit date, the status will change to "Accepted". Then when the name is enrolled, the status will become 'Active', and lastly when the name exit the status will be 'Exited'.

I want to calculate number of each status and be able to filter by date as below:

If I want number in year 2020 quarter 3, the result should be:

#of Referred	#of Accepted	#of Active	#of Exit
2	0	2	0

But if I want number in year 2020 quarter 4, the result should be:

#of Referred	#of Accepted	#of Active	#of Exit
2	1	1	1

How can I achieve this? Appreciate for your help. Thanks,

[daxer-almighty]

This stupid site does not let me insert this HTML into my previous post, hence you see [table here]. Here's the raw HTML. Render it in your browser to see the structure of the table.

<table border="1" width="76%">
<tbody>
<tr>
<td width="15%">Name</td>
<td width="17%">Status</td>
<td width="17%">Date</td>
<td width="9%">Final Status</td>
<td width="19%">Final Status Date</td>
</tr>
<tr>
<td>A</td>
<td>Referred</td>
<td>04/03/2020</td>
<td>Exit</td>
<td>(you know what</td>
</tr>
<tr>
<td>A</td>
<td>Accepted</td>
<td>09/03/2020</td>
<td>Exit</td>
<td>goes in here)</td>
</tr>
<tr>
<td>A</td>
<td>Enrollment</td>
<td>13/03/2020</td>
<td>Exit</td>
<td>&nbsp;</td>
</tr>
<tr>
<td>A</td>
<td>Exit</td>
<td>01/12/2020</td>
<td>Exit</td>
<td>&nbsp;</td>
</tr>
<tr>
<td>B</td>
<td>Referred</td>
<td>05/05/2020</td>
<td>Referred</td>
<td>&nbsp;</td>
</tr>
<tr>
<td>C</td>
<td>Referred</td>
<td>07/07/2020</td>
<td>Enrollment</td>
<td>&nbsp;</td>
</tr>
<tr>
<td>C</td>
<td>Accepted</td>
<td>08/08/2020</td>
<td>Enrollment</td>
<td>&nbsp;</td>
</tr>
<tr>
<td>C</td>
<td>Enrollment</td>
<td>08/09/2020</td>
<td>Enrollment</td>
<td>&nbsp;</td>
</tr>
<tr>
<td>D</td>
<td>Referred</td>
<td>02/09/2020</td>
<td>Accepted</td>
<td>&nbsp;</td>
</tr>
<tr>
<td>D</td>
<td>Accepted</td>
<td>03/10/2020</td>
<td>Accepted</td>
<td>&nbsp;</td>
</tr>
<tr>
<td>E</td>
<td>Referred</td>
<td>08/11/2020</td>
<td>Referred</td>
<td>&nbsp;</td>
</tr>
</tbody>
</table>

[daxer-almighty]

This is how your table should be structured to make easy calculations:

[table here]

To calculate the number of names that have the final status of whatever selection you make, you'll write this measure:

[Name Count] = DISTINCTCOUNT( T[Name] )

But you should also have 2 dimensions for Status and Final Status. Also, there should be a date table for both dates (date fields should be hidden). The above reshaping should be done in Power Query. Just the way I've done it... Can you see how simple the measure is if the model IS RIGHT? Think about it for a while.

[dedelman_clng · Accepted solution]

Hi @Anonymous - 

Make sure you have a calendar/date table and make sure the Year and Quarter slicers are coming from it.  Then you should be able to take this measure for #Referred and adapt it to the other status pairs (Accepted/Enrolled, Enrolled/Exit)

NumReferred = //Referred Date in the Past and Accepted Date is blank or future
VAR __MaxDt =
    IF (
        HASONEVALUE ( DateTab[Year] ) && HASONEVALUE ( DateTab[Quarter] ),
        MAX ( DateTab[Date] )
    )
VAR RefTab =
 FILTER ( StatusChg, StatusChg[Referred Date] <= __MaxDt ) 
VAR AccTab =
        FILTER (
            StatusChg,
            ISBLANK ( StatusChg[Accepted Date] )
                || StatusChg[Accepted Date] > __MaxDt
        )
RETURN
    COUNTROWS ( INTERSECT ( RefTab, AccTab ) ) + 0

 (The +0 at the end is to keep the measure from showing blank)

Hope this helps

David

[Anonymous]

Thank you very much. It works.

One more thing I wanted to ask. If I want to track the time between referred and accepted and have a measure to mark it as "overdue" if referred is greater than 30 days at the period I select from the slicer . How do I do that? Thanks,