Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Project: #1
Start: 1/1-2018
End: 5/1-2018
Total workload: 10 hours
Responsible: Peter
Project: #2
Start: 3/1-2018
End: 5/1-2018
Total workload: 6 hours
Responsible: Peter
Would render the following:
Peters average workload for week 1
Monday: 2 hours
Tuesday: 2 hours
Wednesday: 4 hours
Thursday: 4 hours
Friday: 4 hours
Any tips on this?
//M
Add a calendar table and use measure below.
Measure = VAR d = SELECTEDVALUE ( 'Calendar'[Date] ) RETURN SUMX ( FILTER ( Table1, Table1[Start] <= d && Table1[End] >= d ), Table1[LoadPrWorkDay] )
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,19
Thursday 1,19
Friday 1,19
For alle the days in the project.
Can that be done?
Drag [Date] from calculated table as shown below.
Calendar = CALENDARAUTO ()
@v-chuncz-msft
Uhm 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 🙂
Here's a screenshot of my sample data:
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |