Forum Discussion

RWWRRW's avatar
RWWRRW
Regular Visitor
3 years ago

Days Beetween deliverydates

I have a table with different order numbers, different materials and different delivery dates. I would now like to have the number of days of the delivery date per material evaluated. In Excel I had...
Need Help
Show and Tell
danextian's avatar
danextian
Super User
3 years ago

Please provide more information/sample data. It will matter the days  to compare are in separate tables, different rows, etc.

RWWRRW's avatar
RWWRRW
Regular Visitor
3 years ago

Hi

i have a table with PO/Parts/deliverydate

29.06.2023 
6238447471529.06.20230
6240493505016.06.2023 
6238006505024.06.20238
552230505301.06.2023 
552230505315.06.202314
552230505329.06.202314
62404977890220.06.2023 
62405767890220.06.20230
62409157890227.06.20237
  • RWWRRW's avatar
    RWWRRW
    Regular Visitor
    3 years ago

    sorry i hope this table shows it. I want in Power Bi via Dax calculate the days beetween the deliverdates from each part

    POPartDate LfDdays
    551196471107.06.2023 
    551197471119.06.202312
    82706078471223.06.2023 
    82693981471230.06.20237
    5555125471307.06.2023 
    5555126471322.06.202315
    6238279471529.06.2023 
    6238447471529.06.20230
    6240493505016.06.2023 
    6238006505024.06.20238
    552230505301.06.2023 
    552230505315.06.202314
    552230505329.06.202314
    62404977890220.06.2023 
    62405767890220.06.20230
    62409157890227.06.20237
    • Kishore_KVN's avatar
      Kishore_KVN
      Solution Sage
      3 years ago

      You should have explained it better. Any ways here is the solution.

      Use this measure to get days.

      Duration = 
Var Start_Date = CALCULATE(MIN('Table'[Date LfD]),ALLEXCEPT('Table','Table'[Part]))
Var End_Date = CALCULATE(MAX('Table'[Date LfD]),ALLEXCEPT('Table','Table'[Part]))
Var Result = DATEDIFF(Start_Date,End_Date,DAY)
Return
Result

      Output looks like this:

       

      If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!

       

      • RWWRRW's avatar
        RWWRRW
        Regular Visitor
        3 years ago

        Not quite yet. It now correctly displays the daydiffernz from the first to the last delivery date. But the days between several delivery dates unfortunately not.
        See marked in green.
        For Part 5053 I expected from June 1 to June 15 (first delivery date to next delivery date)14 days difference from June 14 to June 29 (next delivery date to next) the difference of 14 days.
        In the next part I have three delivery dates, here I would have expected as result 0 and in the second the 7 days day difference.
        There can also be several delivery dates per part

         

    • danextian's avatar
      danextian
      Super User
      3 years ago

      Hi RWWRRW

       

      You can use RANKX to rank the dates by part and another calc column to get the previous date

      DateRank = 
RANKX (
    FILTER ( 'Table', 'Table'[Part] = EARLIER ( 'Table'[Part] ) ),
    'Table'[Date LfD],
    ,
    ASC,
    DENSE
)

      PrevDate (RANKX) = 
CALCULATE (
    MAX ( 'Table'[Date LfD] ),
    FILTER (
        FILTER ( 'Table', 'Table'[Part] = EARLIER ( 'Table'[Part] ) ),
        'Table'[DateRank]
            = EARLIER ( 'Table'[DateRank] ) - 1
    )
)

      Or a single calc column to for the previous date

      PrevDate (not RANKX) = 
CALCULATE (
    MAX ( 'Table'[Date LfD] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Part] ),
        'Table'[PrevDate (RANKX)] < EARLIER ( 'Table'[PrevDate (RANKX)] )
    )
)

      For larger tables, I prefer the RANKX version. Performance shouldn't be an issue with small tables.

       

      Now to get the difference, you can use either of the two previous date column

      Difference = 
IF ( 'Table'[PrevDate (RANKX)] = BLANK (), 0, 'Table'[Date LfD] - 'Table'[PrevDate (RANKX)] )