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:CASEWHEN "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'ENDIn 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 slicerVAR 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-31CustomerSoldDateContractIssuedDateSettlementDateCancelledDateStatus as at 31-Jan-25Murphy Collier3/06/202423/06/2024 Contract IssuedEdison Fleming22/04/202422/01/2025 6/02/2025Contract IssuedFatima Meza N/ALucian Walsh14/08/2024 SoldLeia Preston13/11/2024 14/02/2025SoldVincenzo Gilbert25/03/2024 31/01/2025CancelledJocelyn Jefferson12/12/202422/01/20253/02/2025 Contract IssuedRaylan Guzman24/02/20243/11/20249/01/2025 SettledAshley Andrade27/03/202413/01/2025 26/01/2025CancelledAbdiel Giles8/10/202415/01/202518/02/2025 Contract Issued

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

Haydos

Advocate I

1 year ago

Solved

Dynamic Status Calculation

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

Need Help

Anonymous

1 year ago

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

sample.pbix100 KB

3 Replies

Ritaf1983
Super User
1 year ago
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
  Advocate I
  1 year ago
  Thanks Rita, I've added some sample data to my original post now.

Anonymous

Not applicable

1 year ago

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

sample.pbix100 KB

Forum Discussion

Dynamic Status Calculation

3 Replies

Related content

Dynamic Rank Calculation

Dynamic Calculation Different Period

Dynamic Variable in Calculated Columns

Calculate # of days in each status

Dynamic Calculation Group Item