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 🙂
Kudos to you if you earned one of these! Check your inbox for a notification.
Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.
Find out where you can attend!