Find the difference between two dates incorporating a deadline date

[lennox25]

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.

[Ritaf1983]

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]

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

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

[lennox25]

Perfect - Thank you for ALL your help on this one 🙂

[Anonymous]

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]

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

[Ritaf1983]

percent from which variable? 

[lennox25]

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

[Ritaf1983]

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.

[lennox25]

Thank you - I will take a look at the PBI report you attached and hope it works 🙂 Will let you know