Dynamic Status Calculation

Hi Haydos,

I modify the formula to get each status based on current date fields and merge them to show as text.

Record Status = 
VAR SelectedDate =
    MAX ( DateTable[Date] )
VAR CancelledDate =
    MAX ( T1[CancelledDate] )
VAR SettlementDate =
    MAX ( T1[SettlementDate] )
VAR ContractIssuedDate =
    MAX ( T1[ContractIssuedDate] )
VAR SoldDate =
    MAX ( T1[SoldDate] )
VAR isCancelled =
    IF (
        CancelledDate <= SelectedDate
            && NOT ISBLANK ( CancelledDate ),
        "Cancelled"
    )
VAR isSettled =
    IF (
        SettlementDate <= SelectedDate
            && NOT ISBLANK ( SettlementDate ),
        "Settled"
    )
VAR isContractIssued =
    IF (
        ContractIssuedDate <= SelectedDate
            && NOT ISBLANK ( ContractIssuedDate ),
        "Contract Issued"
    )
VAR isSold =
    IF ( SoldDate <= SelectedDate && NOT ISBLANK ( SoldDate ), "Sold" )
VAR merged =
    IF ( isCancelled <> BLANK (), isCancelled & ";" )
        & IF ( isSettled <> BLANK (), isSettled & ";" )
        & IF ( isContractIssued <> BLANK (), isContractIssued & ";" )
        & IF ( isSold <> BLANK (), isSold )
RETURN
    IF ( merged <> BLANK (), merged, "N/A" )

Then you can add a new table with all status types and use it as category of the table visual and write a measure formula to count correspond status from the concatenated text.

Status Count = 
VAR currStatus =
    SELECTEDVALUE ( NewTable[Status] )
VAR summary =
    SUMMARIZE (
        ALLSELECTED ( T1 ),
        [Customer],
        [SoldDate],
        [ContractIssuedDate],
        [SettlementDate],
        [CancelledDate],
        "Status", [Record Status]
    )
RETURN
    COUNTROWS (
        FILTER ( summary, SEARCH ( currStatus, [Record Status], 1, -1 ) > 0 )
    )

Regards,

Xiaoxin Sheng