Help Need to find Day delta in either Power Query or DAX formuale

[NH]

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

[ofirk]

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)

[NH]

Hi 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]), 

[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 · Accepted solution]

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

[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