Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

1 REPLY 1
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.