Forum Discussion
Status change and count
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,
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
4 Replies
- dedelman_clng
Community Champion
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
- daxer-almighty
Solution Sage
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.
- 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> </td>
</tr>
<tr>
<td>A</td>
<td>Exit</td>
<td>01/12/2020</td>
<td>Exit</td>
<td> </td>
</tr>
<tr>
<td>B</td>
<td>Referred</td>
<td>05/05/2020</td>
<td>Referred</td>
<td> </td>
</tr>
<tr>
<td>C</td>
<td>Referred</td>
<td>07/07/2020</td>
<td>Enrollment</td>
<td> </td>
</tr>
<tr>
<td>C</td>
<td>Accepted</td>
<td>08/08/2020</td>
<td>Enrollment</td>
<td> </td>
</tr>
<tr>
<td>C</td>
<td>Enrollment</td>
<td>08/09/2020</td>
<td>Enrollment</td>
<td> </td>
</tr>
<tr>
<td>D</td>
<td>Referred</td>
<td>02/09/2020</td>
<td>Accepted</td>
<td> </td>
</tr>
<tr>
<td>D</td>
<td>Accepted</td>
<td>03/10/2020</td>
<td>Accepted</td>
<td> </td>
</tr>
<tr>
<td>E</td>
<td>Referred</td>
<td>08/11/2020</td>
<td>Referred</td>
<td> </td>
</tr>
</tbody>
</table>
