Forum Discussion
DAX - Combine WEEKDAY and TIME functions to filter specific date/time ranges
I am building a production schedule and am trying to set up shifts to plan production or not. I used the below FILTER to designate production between 6am and 10pm on weekdays. For another equipment center, I'd like to setup a shift that doesn't operate past 2pm on Fridays (they run a 4/10 schedule). Could someone help me combine the WEEKDAY and TIME filters to account for this?
var _table =
TOPN(
hours + 1,
FILTER(
'Production Gantt',
'Production Gantt'[Value] > firststartdate &&
('Production Gantt'[Value] - INT('Production Gantt'[Value])) > TIME(6,0,0) &&
('Production Gantt'[Value] - INT('Production Gantt'[Value])) < TIME(22,0,0) &&
WEEKDAY('Production Gantt'[Value])<>1 &&
WEEKDAY('Production Gantt'[Value])<>7
),
[Value],
ASC
) return //schedule from 6am through 10pm start times on weekdays.
2 Replies
- amitchandak
Super User
krwalke , Not very clear. Are you looking for business hours?
https://exceleratorbi.com.au/calculating-business-hours-using-dax/
The information you have provided is not making the problem clear to me. Can you please explain with an example.
Appreciate your Kudos.- krwalkeFrequent Visitor
The above FILTER code is written and filters another date table ('Production Gantt' - generated list of dates for the next 365 days in units of minutes) by the following:
- time is greater than 6am
- time is less than 10pm
- the day is not Sunday
- the day is not Saturday
What I'm looking to add to that is:
- the day is not Friday after 2pm
Thanks for the reply and help!