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 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.