Calculate a working week date range inside a broader day range considering different calendar reason

[Mike22]

Dear Friends,

I am trying to check if a date range is included in another range of dates from another table.

I have two tables

Actual travel – Which shows the actual dates a “person id” was travelling

Travel Requested- Which shows the dates for which travel was requested.

What I want to come up with is the calculated column “Travel dates authorized” on the "actual travel" table but I am not sure how to do it in DAX

I need to check if the “Actual travel” was fully included in the “Travel Requested” noting that travel requested only covers working days. The region determines weather the working week is Mon-Fri or Sun-Thu. If “Middle East” it is Sun-Thu

If the “actual travel” start date occurs over the weekend (sat-sun or fri-sat) there has to be a “travel requested” entry for the next working day.

Same if “actual travel”  end occurs on a weekend there has to be a “travel requested” entry for the previous working day.

If the travel authorized range is not in the travel requested or if there is no travel requested entry for the person ID on the specific dates “travel dates authorized” = NO

Thanks for the support,
Mike

[Mike22]

Hi @CheenuSing ,

The result is actually not a table but column "travel dates authorized" highlighted in yellow which is a column which I would like to add to the "actual travel" table. 

The idea is that I need to check if an entry was raised for the "person ID" in the "travel requested" table covering the full range of working days from the "actual travel" start to end. If all weekdays are covered than the result is "yes".

The problem is that the "travel requested" start or end dates might be on a weekend if the input was not done correctly so it is not sufficient to check if the closest weekday to the "actual travel" is included in the "travel requested range".

The other problem is that the working week is different for the entries marked as Middle East as it goes from Sunday to Thursday and not Monday to Friday. 

Hope this clarifies.

Thanks for your interest in helping me solve this issue.

Mike

[Mike22]

@CheenuSing 

Do you have any idea on how this can work? Thanks. 

[Anonymous]

Hi @Mike22 ,

It seems like your table contains multiple same user request, I'd like to suggest you add index column to these table to define each request.

After these steps, I think you can use index and user id to find out correspond records to compare with these records to check if actual date range are into request ranges.
Then you can if statement with weekday function(use WeekDay on start/end date to extract weekday) to compare with conditions and return 'IN' result.

Regards,

Xiaoxin Sheng

[Mike22]

Hello @Anonymous 

Thank you for your reply. Is it possible to give me a bit more detail?

I can create an index table with unique person IDs but how do I then check all the dates to see if there is a  "travel requested" entry covering the the "actual travel" period. 

Thanks,

Mike

[CheenuSing]

Hi @Mike22 

Can you explain step by step  how you arrived at the final table.

Like what you compared with what ?

Cheers

CheenuSing