Forum Discussion

lennox25's avatar
lennox25
Icon for Post Patron rankPost 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 ...
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

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
Icon for Post Patron rankPost 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
    Icon for Super User rankSuper User
    2 years ago

    percent from which variable? 

    • lennox25's avatar
      lennox25
      Icon for Post Patron rankPost Patron
      2 years ago

      Hi Ritaf1983 , I am trying to acheive the below which is in the smaller of the boxes.

       

      • Ritaf1983's avatar
        Ritaf1983
        Icon for Super User rankSuper User
        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.