topic Sum assigned hours between 2 dates and also display grand total against each row in DAX Commands and Tips

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

Deevo_ — Thu, 09 Jan 2025 07:41:01 GMT

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

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

xifeng_L — Thu, 09 Jan 2025 09:14:12 GMT

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~

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

Deevo_ — Fri, 10 Jan 2025 07:20:44 GMT

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

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

Deevo_ — Mon, 13 Jan 2025 00:30:08 GMT

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