cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

DateTime calculation with cut-off time, working days & Holidays per Province

Hi Everyone !!

I need your help regarding complicated "date & time" calculations rules with hours, working days and holidays (with different holidays per zone/Province).

I know there is a lot of topics here about dates & hours calculation but I have not seen/found a case like mine.

You will find below some screenshots describing the Data Model and the Tables (please do not hesitate to tell me if some tables are useless ; for example, I am not sure if a Calendar table or Time table is necessary in this case??).

The target is to define if an order is "on time" or "late" between the DATE1 (start date) and the DATE2 (end date). This could be one column with a binary code like 1 = "on time" and 0 = "late" in the results.

All the orders with a DATE1 < 4:00pm must be ended (DATE2) the same day.
All the orders with a DATE1 >= 4:00pm must be ended (DATE2) the same day or the day after the day in DATE1.
All the other orders will be considered "late".

Hope all this is clear enough ; please do not hesitate to revert to me if you need more details.

Many thanks for your help !

The problem has been solved !

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

Re: DateTime calculation with cut-off time, working days & Holidays per Province

I really think that you need to provide some sample data (as text that can be copied and pasted) and expected results from that data. I would just include enough data to highlight your use cases.

There are a lot of measures in the Quick Measures Gallery that do some of this, like:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362

If you are looking for a chart that shows how many orders are on time on any given date (point in time) and I'm not real clear on this, then something like this might assist:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364

Proud to be a Datanaut!

Super User

Re: DateTime calculation with cut-off time, working days & Holidays per Province

I really think that you need to provide some sample data (as text that can be copied and pasted) and expected results from that data. I would just include enough data to highlight your use cases.

There are a lot of measures in the Quick Measures Gallery that do some of this, like:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362

If you are looking for a chart that shows how many orders are on time on any given date (point in time) and I'm not real clear on this, then something like this might assist:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364