Comparing the dates from a CALENDAR table with the dates of another table

[specbk]

Hello all,

for a few hours I am struggling with the following problem:

I have a Calendar Table with unique dates which excludes weekends and holidays. I also have a Sales Table which has multiple sales on the same day. The relationship between Calendar[Date] and Sales[SalesDate] is 1 to n.

I want to create a new column in the Sales Table which should be Sales[SalesDate] + 1. The problem is that if Sales[SalesDate] is Friday then Sales[SalesDate] + 1 is Saturday and I don't want this as a result. In this case I need Sales[SalesDate] + 3 in order to get Monday. I've tried : IF(Sales[SalesDate] + 1 = RELATED(Calendar[Date]), Sales[SalesDate] + 1, Sales[SalesDate] + 3), but it is logical that the value returned is always FALSE since the key is Sales[SalesDate] .  

Is there any way to express "if the values of Sales[SalesDate] + 1 are not present/present in the column Calendar[Date]"?

Thanks in advance!

Best regards

[Anonymous]

Hi @specbk,

Can you please share some dummy data with a similar data structure and expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[specbk · Accepted solution]

Hi @Anonymous ,

after a fierce battle I managed to solve the problem. 

I created a Holiday Calendar (only with holidays) and a normal one. Then I excluded both holidays and weekends and with RANKX I got the "Next business day". 

From my database I have a column which gives me the response time.

Example: I miss a call from my client at 15:00 and I call him at 17:30 (response time). My response time must be max. 4 hours after he has tried to contact me. My workday starts at 08:00 and ends at 18:00. So I have time to contact him until 09:00 on the "Next business day" (from 15:00 till 18:00 are 3h. and we have 1h. transfered to the "Next business day" which starts at 08:00. )

Solution: I created a column which only has 18:00:00 as a value and then I substracted it from the initial customer call time. If it is > than 4h I must call him on the same day and if it is < than 4h I can call him on the " Next business day" + the remaining time from the previous day. 

Finally with a simple IF I created a column which shows the data in both cases.

Thank you! Regards! 😊

[Anonymous]

Hi @specbk,

Glad to hear you find out the solution and share it here. I think they should help others who face a similar scenario.

Regards,

Xiaoxin Sheng

[PhilipTreacy]

Hi @specbk 

Your Date Table should have every date in it between the first and last date in the range.  If you need to exclude weekends from calculations there are other ways.

What do you need to create this new column for in your Sales table?

Regards

Phil

[specbk]

Hi @PhilipTreacy ,

the problem is that I need to exclude holidays as well.

I need the Sales[SalesDate] + 1  in order to create a Sales[InTime] which can be "Yes" or "No" depending if Sales[SalesDate] + 1 < Sales[ResponseTime]. In other words if the response time is after the Sales[SalesDate]  + 1 then we should get Sales[InTime] = "No" and vice versa.