Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
Hi @abounou
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
)
)
Hi @abounou
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
)
)
I've just tried it and it works! Thank you very much!
User | Count |
---|---|
54 | |
23 | |
19 | |
18 | |
17 |
User | Count |
---|---|
91 | |
87 | |
47 | |
30 | |
22 |