Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Morten
Regular 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
v-chuncz-msft
Community Support
Community Support

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

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?

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

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

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

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

Morten
Regular Visitor

Here's a screenshot of my sample data:

SampleData.PNG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.