Forum Discussion

lennox25's avatar
lennox25
Post Patron
2 years ago
Solved

Find the difference between two dates incorporating a deadline date

Hi, 

Please see table.

 

If Date Delivered is 14/11/2023 for Apples the deadline is <=2 days to Date Sold. How do I add a column adding on each deadline day to each category. The list is long and added to everyday. If I can get  columns with the Date Delivered plus the deadline date then I should be able to work out the difference between Datedelivered/Deadline date and Date Sold.  In this case it 2 days over the deadline.

 

 

Need Help
  • Ritaf1983's avatar
    Ritaf1983
    2 years ago

    Hi lennox25 

    You can import those tables to power query and merge between them:

    extract from the string of deadlines only the number of dates :

    modify data type to number

    And add it to the sales date with a custom column

    modify a result as a date data type

    uncheck the option load to the model at the deadlines table ( you don't need it inside)

     

    Pbix is attached

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



  • Ritaf1983's avatar
    Ritaf1983
    2 years ago

    Hi lennox25 
    You can create 3 measures : "
    1.

    total_transactions = COUNTROWS(sales)

    2 

    over_deadline = CALCULATE([total_transactions],FILTER(sales,[over deadline days]<0))

    3 

    %_over = divide ([over_deadline],[total_transactions])
    + Format it as percent

    The updated pbix is attached

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

  • Ritaf1983's avatar
    Ritaf1983
    2 years ago

    Hi lennox25 
    Add a measure :

    in_deadline % = 1-[%_over]

    The updated pbix is attached

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

10 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi lennox25 ,

     

    You can try calculated column like below:

    Deadline Date =
SWITCH (
    TRUE (),
    'YourTable'[Category] = "Apples", 'YourTable'[Date Delivered] + 2,
    'YourTable'[Category] = "Pears", 'YourTable'[Date Delivered] + 2,
    'YourTable'[Category] = "Oranges", 'YourTable'[Date Delivered] + 2,
    'YourTable'[Category] = "Bananas", 'YourTable'[Date Delivered] + 1,
    'YourTable'[Category] = "Grapes", 'YourTable'[Date Delivered] + 8,
    'YourTable'[Date Delivered]
)
    Days Over Deadline =
DATEDIFF ( YourTable[Date Sold], YourTable[Deadline Date], DAY )

     

    Best Regards,
    Adamk Kong

     

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

    • lennox25's avatar
      lennox25
      Post Patron
      2 years ago

      Thats great and seems to work perfect - how would I now go about putting the Days Over deadline into a %?

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    2 years ago

    Hi lennox25 

    You can import those tables to power query and merge between them:

    extract from the string of deadlines only the number of dates :

    modify data type to number

    And add it to the sales date with a custom column

    modify a result as a date data type

    uncheck the option load to the model at the deadlines table ( you don't need it inside)

     

    Pbix is attached

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



    • lennox25's avatar
      lennox25
      Post Patron
      2 years ago

      Hi Ritaf1983 Thank you this seems to work for the over deadline. 

      I've now been asked to work out within deadline days - can you help withi this please?

       

      Thanks

      • Ritaf1983's avatar
        Ritaf1983
        Super User
        2 years ago

        Hi lennox25 
        Add a measure :

        in_deadline % = 1-[%_over]

        The updated pbix is attached

        If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly