Comparing two dates with a tolerance of +- 1 day

[Anonymous]

Hi,

I want to create a calculated column that counts the days between two date columns in the same table with a tolerance of +- 1 day. I am working on a performance dashboard and I want to visualize on time deliveries from a specifik vendor. If the delivery date is greater than the posting date (with a tolerance of +-1  day), the output is "Late", if the delivery date is equal or smaller than the posting date (with a tolerance of +- 1 day, the output is "On Time".

Any tips on how to achive this?

Best

[v-cazheng-msft · Accepted solution]

Hi @Anonymous 

You may try this Calculated Column or Measure.

1 Calculated Column

IsOnTime =

VAR datediff_ =

DATEDIFF ( Table1[Posting_date], Table1[Promised_Receipt_Date], DAY )

RETURN

IF ( datediff_ >= -1, "On Time", "Late" )

2 Measure

IsOnTime_measure =

VAR diff_ =

DATEDIFF( MAX ( Table1[Posting_date] ),MAX ( Table1[Promised_Receipt_Date] ),DAY)

RETURN

IF ( diff_ >= -1, "OnTime", "Late" )

Then, the result should look like this:

Attached the pbix file as reference.

Best Regards,

Community Support Team _ Caiyun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If you still have problems on it or I misunderstand your needs, please feel free to let us know. Thanks a lot!

[AlexisOlson]

Is this what you're trying to write?

Punctuality =
IF ( Table1[DeliveryDate] > Table1[PostingDate] + 1, "Late", "On Time" )

[Anonymous]

Thanks for the reply!

This is part of my table. I added column "Days between" so it will be easier to see the difference in days. So in this case "Days between" with row values of 4 is considered "Late", and the row values of 1 is considered "On Time", hence the 1 day tolerance. If the posting date is 2 days after the delivery date, it is considered "Late".  Unfortunately your solution resulted in all of the rows showing "Late", but something similiar is what i'm looking for.

[AlexisOlson]

If you've got this column, then you should be able to write

IF ( Table1[Days Between] > 1, "Late", "On time" )

[VahidDM]

HI @Anonymous 

Try this:

Measure = 
Var _Dif = Table1[Primised_Receipt_Date] - Table1[Posting_date]return
If(_Dif<=1,"On Time","Late")

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[Anonymous]

Hi!

Thank you for replying.

Weirdly enough I get this result with Punctunality =
Var _Dif = Table1[Promised_Receipt_Date] - Table1[Posting_date]return
If(_Dif<=1,"On Time","Late")

[VahidDM]

Do those date columns have Date foramt?

Try this:

New Column=

If(([Promised_Receipt_Date] - [Posting_date])<=1,"On Time","Late")

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[Anonymous]

Unfortunately I get the same result as before. The two columns have date format