topic Re: Comparing two dates with a tolerance of +- 1 day in DAX Commands and Tips

Comparing two dates with a tolerance of +- 1 day

Anonymous — Sun, 14 Nov 2021 18:54:54 GMT

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

Re: Comparing two dates with a tolerance of +- 1 day

AlexisOlson — Sun, 14 Nov 2021 19:09:54 GMT

Is this what you're trying to write?

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

Re: Comparing two dates with a tolerance of +- 1 day

Anonymous — Sun, 14 Nov 2021 19:56:37 GMT

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.

Re: Comparing two dates with a tolerance of +- 1 day

VahidDM — Sun, 14 Nov 2021 21:43:00 GMT

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/

Re: Comparing two dates with a tolerance of +- 1 day

Anonymous — Sun, 14 Nov 2021 22:03:24 GMT

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")

Re: Comparing two dates with a tolerance of +- 1 day

VahidDM — Mon, 15 Nov 2021 04:30:33 GMT

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/

Re: Comparing two dates with a tolerance of +- 1 day

AlexisOlson — Mon, 15 Nov 2021 15:54:25 GMT

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

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

Re: Comparing two dates with a tolerance of +- 1 day

Anonymous — Mon, 15 Nov 2021 16:33:23 GMT

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

Re: Comparing two dates with a tolerance of +- 1 day

v-cazheng-msft — Wed, 17 Nov 2021 04:46:17 GMT

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!