Say I have a table of projects with start date, end date, total workload and responsible. I want to be able to show the average workload on all responsible for each day in the range.A very simple case:
Start: 1/1-2018End: 5/1-2018Total workload: 10 hoursResponsible: Peter
Project: #2Start: 3/1-2018End: 5/1-2018
Total workload: 6 hoursResponsible: PeterWould render the following:Peters average workload for week 1Monday: 2 hoursTuesday: 2 hoursWednesday: 4 hoursThursday: 4 hoursFriday: 4 hoursAny tips on this?//M
Here's a screenshot of my sample data:
Add a calendar table and use measure below.
VAR d =
SELECTEDVALUE ( 'Calendar'[Date] )
FILTER ( Table1, Table1[Start] <= d && Table1[End] >= d ),
Thanks! When you say a calendar table do you mean a data table containing of just dates? And if so does it need to be linked to the project table or not?
Yes, no need to set relationship.
I'm getting closer, but it does not seem to do quite what I'm looking for still.In week 1 Peter has a project, which starts the 3rd of january and ends the 31st of january. This is what I'm getting:But I'm really needing it to make it look like this:Wednesday 1,19Thursday 1,19Friday 1,19For alle the days in the project.Can that be done?
Drag [Date] from calculated table as shown below.
@v-chuncz-msftUhm I might not fully understand what you're asking me to do, but now I've created yet another table, but that didn't really do much of a difference?
Still looking for input to get the solution I need 🙂
Join us in the third Triple A event!
It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.
Make sure you didn't miss any of the things that happened in the community in January!