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 UserKudos 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
This was a big help! I went down a rabbit hole of SUMX which worked but wasnt very efficient and would lock up my larger dataset.
One thing I'm still struggling with is making this a calculated column. When I bring it to the column it doesnt add up correctly. it should match the 'remain_drtn_hr_cnt' column.
see here:
though it works find in a mtrix view seen here:
what am I doing wrong? I feel like its something simple but Im missing it.
P.S. Ive reworked your formula to take into account calendar type (7 day, 6, 5 etc. and I have added in an exceptions column to the 'DATES' table as I need it to skip holidays as determined by the calendar.
Many thanks for the help so far!
- lbendlin
Sure, you could make that a calculated column, but that would mean you need to use a CROSSJOIN and would potentially use up a lot of storage. Are you sure you want to go that route?
I suppose I dont want that. I'm ultimately trying to determine remaining Cost per hour and to show that in bar chart form by month / a matrix like my picture in my original post where by finding cost per hour I could then see how much cost was in a day.
So that matrix would show the daily remaining cost by activity and the bar chart would show the monthly total of all activities. I need to know I'm projecting to bill X amount in the next month and X amount the next month etc.
So if I can get this same result with a calculation and take up less storage than I'd love to know how to accomplish that.
column chart would look something like this style
