Forum Discussion

NH's avatar
NH
Icon for Advocate II rankAdvocate II
8 years ago
Solved

Help 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 o...
Need Help
  • ofirk's avatar
    ofirk
    8 years ago

    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's avatar
NH
Icon for Advocate II rankAdvocate II
8 years ago

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.

IndexShipment IDShipment Date ChangeDay_delta in DAXCorrect Result should be
085309/12/1861
185309/13/1865
285309/18/18NANA
345009/18/18-4-1
445009/17/18-4-2
545009/15/18-4-1
645009/14/18NANA
ofirk's avatar
ofirk
Icon for Resolver II rankResolver II
8 years ago

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's avatar
    NH
    Icon for Advocate II rankAdvocate II
    8 years ago

    Hi Ofirk,

     

    The updated Dax code is working. Thanks you every much.

     

    Best regards,

    NH