Forum Discussion
Advocate IIHelp Need to find Day delta in either Power Query or DAX formuale
Hi
I've a shipment data which need to find the day different for a given same shipment ID where its Shipment date changes.
In Excel I'm using this formaule the get the result but like to find out how do it in Power BI. Can anyone help to advise. Thanks in advance.
| Shipment ID | Shipment Date Change | Day_delta Result | Forumale in Excel | Purpose |
| 853 | 9/12/2018 | 1 | IFERROR(DAYS360(B2,VLOOKUP(A2,A3:B49981,2,FALSE)),"NA") | Find the Day different for a given Shipmen ID |
| 853 | 9/13/2018 | NA | IFERROR(DAYS360(B3,VLOOKUP(A3,A4:B49982,2,FALSE)),"NA") | |
| 298 | 9/18/2018 | NA | IFERROR(DAYS360(B4,VLOOKUP(A4,A5:B49983,2,FALSE)),"NA") | |
| 450 | 9/18/2018 | -1 | IFERROR(DAYS360(B5,VLOOKUP(A5,A6:B49984,2,FALSE)),"NA") | |
| 450 | 9/17/2018 | NA | IFERROR(DAYS360(B6,VLOOKUP(A6,A7:B49985,2,FALSE)),"NA") | |
| 478 | 9/15/2018 | -1 | ||
| 478 | 9/14/2018 | NA | ||
| 666 | 9/19/2018 | 1 | ||
| 666 | 9/20/2018 | NA | ||
| 604 | 9/21/2018 | 1 | ||
| 604 | 9/22/2018 | NA | ||
| 525 | 9/20/2018 | -2 | IFERROR(DAYS360(B13,VLOOKUP(A13,A14:B49992,2,FALSE)),"NA") | |
| 525 | 9/18/2018 | NA | ||
| 342 | 9/17/2018 | 3 | ||
| 342 | 9/20/2018 | NA | ||
| 344 | 9/17/2018 | 3 | ||
| 344 | 9/20/2018 | NA |
Best Regards,
NH
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
7 Replies
ofirkResolver II
Hi,
I'll assume you have some column that contains the original shipment date.
How about creating the following column:
Day Delta Result = DATEDIFF([Original Shipment Date], [Shipment Date Change], DAY)
NHHi 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.
- ofirk
Hi 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]),