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
Advocate IIHi Ofirk,
The original shipment date will be in the same column as shipment date change. That why need know how to resolve this in Power Bi. Thanks you.
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]),
- NH
Hi Ofirk,
I've tried your solution and the result was very very close to the result in Excel. I'm still trying to unerstand your DAX code and also trying to find out why some row the Day delta was different from the result in Excel.
Thanks for your help. Cheers.
Regards,
NH
- NH
Hi 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