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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Deevo_
Resolver I
Resolver I

Sum assigned hours between 2 dates and also display grand total against each row

Hi all,

I have an existing measure which calculates Assigned hours between a start and end date. This works for each single row.

 

Assignment Hours between start and end dates =
CALCULATE (
SUM ( 'Assignments'[HoursEffort] ),
FILTER (
'Dim_Date',
'Dim_Date'[Date] >= MIN('Assignments'[StartDate] )
&& 'Dim_Date'[Date] <= MAX ('Assignments'[EndDate] )
)

)

 

What i need help with:

  • I need to modify the measure to give me a "Grand Total Assigned hours" for the same task if it is entered multiple times with a different start and end date.
  • In the below table I expect a "Grand Total Assigned hrs" of 20 against each row. Project Task Assigned Staff StartDate EndDate HoursEffort Grand Total Assigned hrs Project 1 Task 1 John 01/01/2025 31/01/2025 10 20 Project 1 Task 1 John 01/02/2025 28/02/2025 10 20

How can I modify the measure to give me the expected results? Or do i need to do some sort of grouping to combine the 2 rows?

 

Many thanks in advance

 

1 ACCEPTED SOLUTION
xifeng_L
Super User
Super User

Hi @Deevo_ 

 

You can try below measure.

 

Assignment Hours between start and end dates =
CALCULATE (
    SUM ( 'Assignments'[HoursEffort] ),
    ALLEXCEPT ( 'Assignments', 'Assignments'[Project Task], 'Assignments'[Assigned Staff] )
)

 

 

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

 

Thank you~

View solution in original post

3 REPLIES 3
Deevo_
Resolver I
Resolver I

Hi again @xifeng_L

I am really hoping you can help with this. I need to enhance this DAX formula to calculate the staff availability between "Assignment start and end dates". There are 2 datasets that need to be taken into account.

 

For example:

  • I have 2 datasets ('Staff capacity' and 'Staff Assignments')
  • Staff capacity = the hours they are contracted to work each day. (e.g. 8 hours per day for contractors).
  • Staff Assignments = the hours per task assigned to the staff. Staff can be assigned more than 1 task within the same start and end dates, so the formula will need to check all rows and add those together to get a grand total of Assignments between the start and end dates.
  • Then the formula needs to calculate the 'Availaibility' between the Assignment Start and End Dates, i need to work out how much availbility each staff member has. (Availability = Supply - Grand Total Assignments)

Below is my current measure that does a row by row calculation. This needs to be modified to add the "Grand Total Assignments" component:

 

Assignment Period Availability between Assign Start and End dates =
CALCULATE(
[Staff Capacity (hrs) - Assignment (hrs)],
FILTER (
'Dim_Date',
'Dim_Date'[Date] >= MIN('Assignments'[Assignment Start Date]) && Dim_Date[Date] <= MAX('Assignments'[Assignment Finish Date])
)
)

 

Many thanks in advance

xifeng_L
Super User
Super User

Hi @Deevo_ 

 

You can try below measure.

 

Assignment Hours between start and end dates =
CALCULATE (
    SUM ( 'Assignments'[HoursEffort] ),
    ALLEXCEPT ( 'Assignments', 'Assignments'[Project Task], 'Assignments'[Assigned Staff] )
)

 

 

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

 

Thank you~

Thanks @xifeng_L This has put in the right direction to get my expected results. I appreciate your time

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!