Date range even distribution

[Morten]

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

[v-chuncz-msft]

@Morten,

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]
    )

[Morten]

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?

[v-chuncz-msft]

@Morten,

Yes, no need to set relationship.

[Morten]

@v-chuncz-msft

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?

[v-chuncz-msft]

@Morten,

Drag [Date] from calculated table as shown below.

Calendar =
CALENDARAUTO ()

[Morten]

@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?

[Morten]

Still looking for input to get the solution I need 🙂

[Morten]

Here's a screenshot of my sample data: