DAX check later status of contract

[Anonymous]

I have the following problem that I would love to solve in DAX.

Here is the data:

Contract DATE STATUS
C0001 06/08/2019 S0001
C0001 27/09/2019 S0002
C0004 07/09/2019 S0001
C0004 10/10/2019 S0002

Given a time range for instance August and September 2019 , I need to know the contracts that have been in S001 but got no later S0002 (within the date range). In this case solution would C0004.

Thank you for your help in advanced 🙂

[Anonymous]

The metric is per contract CXXXX and the solution would be:

Contract | Date              |  STATUS | Metric

C0004     |  07/09/2019 |   S0001   | 1

if we get rid of contract, i should count the number of contracts that has been on S0001 but no S0002 within the time range

because its the latests row that has been on S0001 but no S0002 in the given filter range.

[Anonymous]

I have achieved the following 

TEST =

var S0001 = CALCULATE(DISTINCTCOUNT(OperationsStatusHistory[CONTRACT_ID]);

'Status'[STATUS_NK] = "S0001";

FILTER('StatusDate'; 'StatusDate'[MONTH_ID] IN VALUES('Date'[MONTH_ID]))

)

var S0002 = CALCULATE(DISTINCTCOUNT(OperationsStatusHistory[CONTRACT_ID]);

'Status'[STATUS_NK] = "S0002";

FILTER('StatusDate'; 'StatusDate'[MONTH_ID] IN VALUES('Date'[MONTH_ID]))

)

RETURN S0001 = 1 && S0001 = 0

Its works ok when contract is rows in a table but when used in a KPI box it always says 0  ;_;

[dax]

Hi gonzalomoran, 

Did you want to get below result?

You could try below measure

Measure 2 =
VAR maxd =
    CALCULATE ( MAX ( 'Table'[DATE] ), ALLEXCEPT ( 'Table', 'Table'[STATUS] ) )
RETURN
    CALCULATE (
        MIN ( 'Table'[Contract] ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[STATUS] ), 'Table'[DATE] = maxd )
    )

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.