Forum Discussion

RonaldvdH's avatar
RonaldvdH
Post Patron
2 years ago

Function Related doesn't seem to work

Hey guys,   Im working on a formula that calculates the amount of hours between 2 dates based on working days. I've been wachting a clip on how to do that and i'm using this formula   Doorloopt...
Need Help
RonaldvdH's avatar
RonaldvdH
Post Patron
2 years ago

lbendlin the sample data was in the picture basically it's a couple of columns with a startdate/time and an enddate/time and then calculate the number of business hours between the two dates

 

For example:

 

WMS_IDStartDateStartDateTimeEndDateEndDateTimeBusinessHours
WMS000512325maandag 14 november 202214:22:00donderdag 17 november 202216:20:00 
WMS000512326maandag 14 november 202215:33:00donderdag 17 november 202220:54:00 
WMS000512327maandag 14 november 202219:00:00woensdag 16 november 202216:15:00 
WMS000512328dinsdag 15 november 202214:22:00donderdag 17 november 202212:04:00 

 

Business Hours are:
Monday till Friday 08:00 - 20:00

Saterday 08:00 - 12:00

lbendlin's avatar
lbendlin
Super User
2 years ago

Thanks. Does it need to be DAX or is Power Query ok too?

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    The date table has nothing to do with your scenario.  You need a Minutes table, but my last version removed that requirement too.

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    As I said before, a MinuteCalendar is not required any more. Only the intervals are considered (cardinality reduction), so that will be faster.  But you can try materializing that table. There's no good reason to recreate it for every row (other than that the code looks slicker).

  • RonaldvdH's avatar
    RonaldvdH
    Post Patron
    2 years ago

    lbendlin Preferably DAX i think

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    see attached. Can be further simplified if you want to avoid creating the MinuteCalendar table.  There will be a performance penalty for large date ranges.

     

     

  • RonaldvdH's avatar
    RonaldvdH
    Post Patron
    2 years ago

    lbendlin I'm not sure your solution will work because I have a 1 to many relationship between the date Table and Incident table.

  • RonaldvdH's avatar
    RonaldvdH
    Post Patron
    2 years ago

    lbendlin in your formula there is a part : GeneratesSeries(0,1439)

    However seeing that my data has 2 years worth of dates and if I make a MinuteCalendar that table would have 1.051.200 rows I don't think that does wonders for the performance right ?

     

    Ive already tried change the value tot a 1.051.200 but my laptop is still busy .... hhaha