Dynamic Status Calculation

[Haydos]

Hi everyone,

I'm fairly new to Power BI, currently migrating from Looker. I'm facing a challenge in trying to determine the status of sales dynamically based on a user-selected reporting date. Here is how the calculation looks in LookML:

CASE
WHEN "cancellation_date" <= "reporting_period_end_date" THEN 'Cancelled'

WHEN "settled_date" <= "reporting_period_end_date" THEN 'Settled'
WHEN "contract_issued_date" <= "reporting_period_end_date" THEN 'Contract Issued'
WHEN "deposit_date" <= "reporting_period_end_date" THEN 'Deposit'
ELSE 'N/A'
END

In Power BI, I have the [Reporting Period End Date] calculating based on a slicer selection of 'Date'[Date]. My current measure calculation is this:

Record Status =

VAR SelectedDate = ([Reporting Period End])  -- The selected Reporting Period End Date from slicer

VAR CancelledDate = MAXX('Sales', [Contract Cancelled Date])

VAR SettlementDate = MAXX('Sales', [Actual Settlement Date])

VAR ContractIssuedDate = MAXX('Sales', [Contract Issued Date])

VAR SoldDate = MAXX('Sales', [Sold Date]) 

RETURN

    SWITCH(

        TRUE(),

        CancelledDate <= SelectedDate && NOT ISBLANK(CancelledDate), "Cancelled",

        SettlementDate <= SelectedDate && NOT ISBLANK(SettlementDate), "Settled",

        ContractIssuedDate <= SelectedDate && NOT ISBLANK(ContractIssuedDate), "Contract Issued",

        SoldDate <= SelectedDate && NOT ISBLANK(SoldDate), "Sold",

        "N/A"

)

This seems to work well if I add it to a visual along with sufficient row-level detail (e.g. adding the customer's name shows the correct status). However, what I'd like to do is list each of the four statuses and provide a count of the rows within each. When I remove the customer name though, only "Cancelled" appears as the status with all rows counting under it. 

Any suggestions on how I can accomplish this? 

Below is some randomly generated sample data. The last column is what I'd expect to see, given a Reporting Date of 2025-01-31

Customer	SoldDate	ContractIssuedDate	SettlementDate	CancelledDate	Status as at 31-Jan-25
Murphy Collier	3/06/2024	23/06/2024			Contract Issued
Edison Fleming	22/04/2024	22/01/2025		6/02/2025	Contract Issued
Fatima Meza					N/A
Lucian Walsh	14/08/2024				Sold
Leia Preston	13/11/2024			14/02/2025	Sold
Vincenzo Gilbert	25/03/2024			31/01/2025	Cancelled
Jocelyn Jefferson	12/12/2024	22/01/2025	3/02/2025		Contract Issued
Raylan Guzman	24/02/2024	3/11/2024	9/01/2025		Settled
Ashley Andrade	27/03/2024	13/01/2025		26/01/2025	Cancelled
Abdiel Giles	8/10/2024	15/01/2025	18/02/2025		Contract Issued

[Anonymous · Accepted solution]

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

[Ritaf1983]

Hi @Haydos 
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Haydos]

Thanks Rita, I've added some sample data to my original post now.