Get the number of Orders that have had their dates modified

[Anonymous]

Hello everyone,
I have an orders history table where orders are logged in it at the end of each day and I want to calculate the number of orders that have had their Delivery date changed between two selected history dates. In the sample below the measure should return 2.

Order Id	Delivery date	History date
20567	30/06/2023	30/06/2023
20566	19/06/2023	30/06/2023
20565	20/06/2023	30/06/2023
20567	20/06/2023	25/06/2023
20566	20/06/2023	25/06/2023
20565	20/06/2023	25/06/2023

I have tried the followinf DAX measure but it's not working since I can't use CALCULATE in a True/False statement.

Date modified = 
VAR _RefDate =
    MIN ( 'Reference Date'[Date] )
VAR _CompDate =
    MIN ( 'Comparaison Date'[Date] )
VAR FilteredIDs =
    CALCULATETABLE (
        VALUES ( 'Orders history'[Order Id] ),
        'Orders history'[History date] = _RefDate
    )
VAR ModifiedIDs =
    CALCULATETABLE (
        VALUES ( 'Orders history'[Order Id] ),
        'Orders history'[History date] = _CompDate
            && 'Orders history'[Order Id]
                IN FilteredIDs
                    && 'Orders history'[Delivery date]
                        <> CALCULATE (
                            MAX ( 'Orders history'[Delivery date] ),
                            ALLEXCEPT ( 'Orders history', 'Orders history'[Delivery date] ),
                            'Orders history'[History date] = _RefDate
                        )
    )
RETURN
    COUNTROWS ( ModifiedIDs )

 I could really use your help and thank you in advance.

[tamerj1 · Accepted solution]

Hi @Anonymous 

please try

Date modified =
VAR _RefDate =
    MIN ( 'Reference Date'[Date] )
VAR _CompDate =
    MIN ( 'Comparaison Date'[Date] )
RETURN
    SUMX (
        VALUES ( 'Orders history'[Order Id] ),
        INT (
            COUNTROWS (
                CALCULATETABLE (
                    VALUES ( 'Orders history'[Delivery date] ),
                    'Orders history'[History date] IN { _RefDate, _CompDate }
                )
            ) > 1
        )
    )

[Anonymous]

I've just tried it and it works! Thank you very much!