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

[Deevo_]

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

[Deevo_]

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 · Accepted solution]

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~

[Deevo_]

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