Forum Discussion
Post PatronFunction Related doesn't seem to work
Post Patronlbendlin 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_ID | StartDate | StartDateTime | EndDate | EndDateTime | BusinessHours |
| WMS000512325 | maandag 14 november 2022 | 14:22:00 | donderdag 17 november 2022 | 16:20:00 | |
| WMS000512326 | maandag 14 november 2022 | 15:33:00 | donderdag 17 november 2022 | 20:54:00 | |
| WMS000512327 | maandag 14 november 2022 | 19:00:00 | woensdag 16 november 2022 | 16:15:00 | |
| WMS000512328 | dinsdag 15 november 2022 | 14:22:00 | donderdag 17 november 2022 | 12:04:00 |
Business Hours are:
Monday till Friday 08:00 - 20:00
Saterday 08:00 - 12:00
Super UserThanks. Does it need to be DAX or is Power Query ok too?
- lbendlin
The date table has nothing to do with your scenario. You need a Minutes table, but my last version removed that requirement too.
- lbendlin
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).
- lbendlin
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
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