Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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!
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |