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

dedelman_clng's avatar
dedelman_clng
Icon for Community Champion rankCommunity Champion
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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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,