Forum Discussion
Advocate IIHelp Need to find Day delta in either Power Query or DAX formuale
Hi NH,
I didn't realize there could be more than 2 changes.
In this case, this should work (assuming that your data is arranged in order by shipment IDs, like in your example)
Day_delta = IF([Index] <> MAXX(FILTER(Table1, [Shipment ID] = EARLIER([Shipment ID])), [Index]), DATEDIFF( MAXX( FILTER(Table1, [Index] = EARLIER([Index])), [Shipment Date Change]), MAXX( FILTER(Table1, [Index] = EARLIER([Index]) + 1), [Shipment Date Change]), DAY))The results look like this:
Explanation:
For all rows where the Index is not the maximum index for the shipment ID (meaning, not the last index of a shimpent id), I use the DATEDIFF by DAY, with the following variables:
- the date with the current index
- the date with the next index
Resolver IIHi NH,
This solution is a bit messy but it works:
1. I added to the data a [Line Number] column based on the order the data is inserted to the table (because from your data sample it appears that the first of 2 shipment ids is always the planned date and the second is the actual)
2. I created a Day_delta column with the following calculation:
Day_delta =
IF([Line Number] = MINX(FILTER(Table1, [Shipment ID] = EARLIER([Shipment ID])), [Line Number]),
DATEDIFF(
MAXX(
FILTER(Table1,
[Shipment ID] = EARLIER([Shipment ID]) &&
[Line Number] = MINX(
FILTER(Table1,
[Shipment ID] = EARLIER([Shipment ID])),
[Line Number])),
[Shipment Date Change]),
MAXX(
FILTER(Table1,
[Shipment ID] = EARLIER([Shipment ID]) &&
[Line Number] = MAXX(
FILTER(Table1,
[Shipment ID] = EARLIER([Shipment ID])),
[Line Number])),
[Shipment Date Change]),
DAY))For a sample of your data I get the following results:
If you don't want to get the result 0 for Shipment ID 298, change the condition to:
IF([Line Number] <> MAXX(FILTER(Table1, [Shipment ID] = EARLIER([Shipment ID])), [Line Number]),
Advocate IIHi Ofirk,
I've found out why there was result was not tally. Because some of the shipment ID has more than 2 shipment dates changes.
The DAX code that you provided was work only if the shipment ID has 2 shipemet dates changes or less.
Please refer to table below which shown the issues. Possible to advise how to fix the below issue? Thanks you.
| Index | Shipment ID | Shipment Date Change | Day_delta in DAX | Correct Result should be |
| 0 | 853 | 09/12/18 | 6 | 1 |
| 1 | 853 | 09/13/18 | 6 | 5 |
| 2 | 853 | 09/18/18 | NA | NA |
| 3 | 450 | 09/18/18 | -4 | -1 |
| 4 | 450 | 09/17/18 | -4 | -2 |
| 5 | 450 | 09/15/18 | -4 | -1 |
| 6 | 450 | 09/14/18 | NA | NA |
- ofirk
Hi NH,
I didn't realize there could be more than 2 changes.
In this case, this should work (assuming that your data is arranged in order by shipment IDs, like in your example)
Day_delta = IF([Index] <> MAXX(FILTER(Table1, [Shipment ID] = EARLIER([Shipment ID])), [Index]), DATEDIFF( MAXX( FILTER(Table1, [Index] = EARLIER([Index])), [Shipment Date Change]), MAXX( FILTER(Table1, [Index] = EARLIER([Index]) + 1), [Shipment Date Change]), DAY))The results look like this:
Explanation:
For all rows where the Index is not the maximum index for the shipment ID (meaning, not the last index of a shimpent id), I use the DATEDIFF by DAY, with the following variables:
- the date with the current index
- the date with the next index
- NH
Hi Ofirk,
The updated Dax code is working. Thanks you every much.
Best regards,
NH