Calculate days between in week context

[bdarcy]

Hi Everyone!

I'm trying to spread the Est Hours of a task between two dates however show it in a week context. Got this far with the DAX measure where it calculates the HoursperDay required for said task but soon as it's put into a week context, it chooses the first and last day of the date table rather than picking up the task start or due at, respectively.

VAR MinDate = FIRSTDATE(_Date_Table[Date])

VAR MaxDate = LASTDATE(_Date_Table[Date])

RETURN(
SUMX(

FILTER('Asana - Cloud - Tasks',

'Asana - Cloud - Tasks'[Task Start On] <= MaxDate &&

'Asana - Cloud - Tasks'[Task Due At] >= MinDate),

DIVIDE('Asana - Cloud - Tasks'[Est Hours],'Asana - Cloud - Tasks'[WorkingDaysBetweenStartDue])

Screenshots below of what it looks like. Basically need the "Week 22" to come out as 17 because the start dates are mid-week.

Day 1 = 1.6 + 0.2 = 1.8

Day 2 = 1.6 + 0.2 = 1.8

Day 3 = 1.6 + 0.2 = 1.8

Day 4 = 1.6 + 0.2 + 4 = 5.8

Day 5 = 1.6 + 0.2 + 4 = 5.8

Which totals 17. However it's taking the 5.8 x 5 days in the week

For context, we're using Asana's workload feature and want to replicate it in Power BI.

Any help is greatly appreciated!

[Anonymous]

@bdarcy 

The easiest solution is to expand the temporal ranges of tasks (Task Start On and Task Due At) into individual days and pre-calculate everything in Power Query. Then all your troubles will go away as if by magic and the DAX will be as simple as SUM( T[Hours] ), where the table T stores an entry for each day of each task between the start and end date and the Hours column is the calculation you're describing. And, what's even more important, the DAX will never be faster than what it will be in this model.

Just create the right model as outlined above.