cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Morten Frequent Visitor
Frequent Visitor

Date range even distribution

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

8 REPLIES 8
Morten Frequent Visitor
Frequent Visitor

Re: Date range even distribution

Here's a screenshot of my sample data:

SampleData.PNG

Community Support Team
Community Support Team

Re: Date range even distribution

@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]
    )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Morten Frequent Visitor
Frequent Visitor

Re: Date range even distribution

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?

Community Support Team
Community Support Team

Re: Date range even distribution

@Morten,

 

Yes, no need to set relationship.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Morten Frequent Visitor
Frequent Visitor

Re: Date range even distribution

@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:
Workload.PNG

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?

Community Support Team
Community Support Team

Re: Date range even distribution

@Morten,

 

Drag [Date] from calculated table as shown below.

 

Calendar =
CALENDARAUTO ()

 

 

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Morten Frequent Visitor
Frequent Visitor

Re: Date range even distribution

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

Highlighted
Morten Frequent Visitor
Frequent Visitor

Re: Date range even distribution

Still looking for input to get the solution I need 🙂

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 194 members 2,271 guests
Please welcome our newest community members: