Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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/...
  • dedelman_clng's avatar
    dedelman_clng
    5 years ago

    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's avatar
daxer-almighty
Icon for Solution Sage rankSolution Sage
5 years ago

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>