Forum Discussion
Value spread across dates and time based on calendar assignment table
Kudos for taking on such a rather complex topic. You're on the right track but you may want to clean up a bit. Too many dangly bits, too many extra visuals etc.
All you need to start is the Activities table and the (disconnected) Dates table. Then you can process each interval the way you did it - Beginning day, intermediate days, ending day. There are a lot of caveats here that we are going to ignore (for example work starting on a weekend day or starting on a workday at 12pm, beginning and ending on the same day, exact weekend days etc)
Hour_Count_PD = switch(TRUE(), -- beginning and ending on the same day max(ACTIVITIES[Start_date_key])=max(ACTIVITIES[Finish_date_key]),DATEDIFF(min(ACTIVITIES[Start_time_key]),min(ACTIVITIES[Finish_time_key]),HOUR)-if(min(ACTIVITIES[Start_time_key])<time(12,0,0),1,0), -- starting day hours SELECTEDVALUE(DATES[Date])=max(ACTIVITIES[Start_date_key]),DATEDIFF(min(ACTIVITIES[Start_time_key]),TIME(17,0,0),HOUR)-if(min(ACTIVITIES[Start_time_key])<time(12,0,0),1,0), -- ending day hours SELECTEDVALUE(DATES[Date])=max(ACTIVITIES[Finish_date_key]),DATEDIFF(TIME(8,0,0),min(ACTIVITIES[Finish_time_key]),HOUR)-if(min(ACTIVITIES[Finish_time_key])>time(12,0,0),1,0), -- in between days SELECTEDVALUE(DATES[Date])>max(ACTIVITIES[Start_date_key]) && SELECTEDVALUE(DATES[Date])<max(ACTIVITIES[Finish_date_key]) && SELECTEDVALUE(DATES[Day of Week]) in {1,2,3,4,5},8)see attached
Super UserThat is an entirely different question. You are moving from day level granularity to month level, and from individual activities to all activities. This means you will have to use a separate query for your measure, with aggregator functions. (ironically this would have been easier with calculated columns)
Note: Please try to avoid using LookupValue. There is a new function NETWORKDAYS() that you may want to use instead.
How would I use the networkdays function in this scenario because that is vital to make this work accurately on a larger dataset where I need to be flexible on which days to count / not count? Could I use the holidays exceptions in networkdays to exclude the dates that coincide with the calendar type in my exceptions column on the dates table?
I have 2 calendar types in my new dataset that I uploaded where 1 is a 5 day workweek and 1 is a 7 day (uses proj_id-clndr_id 123456 for 7 day). Again though in the full data there can be 6 day, 4 day etc so how do I make the distinction in the networkdays based on that Id?