Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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:
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
Solved! Go to 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~
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:
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
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