Calculate remaining capacity

Question

I'm trying to work out remaining capacity of all employees per week Each employee has a maximum capacity per week of 5 The table name is Utilisation_Month the columns within the table are Name|Week_Comm|Days_Worked_|Role|Employee Start Date|Employee End Date|Index| &nbsp;An employee can have multiple entries within a week, so this script should also take into consideration if an employee this into account and calculate the remaining capacity per week based on the first occurrence of that week and employee, It should also take into consideration whether or not the employee started working in the team already based off the employee start date and employee end date fields.&nbsp;&nbsp;This is what I have attempted so far but the value it's returning is incorrect&nbsp;&nbsp;Remaining Capacity per Employee =Remaining Capacity per Employee = VAR MaxCapacityPerWeek = 5 RETURN SUMX( FILTER( Utilisation_Month, Utilisation_Month[Week_Comm] = MAX(Utilisation_Month[Week_Comm]) // Adjust based on your calendar selection ), IF( COUNTROWS( FILTER( Utilisation_Month, Utilisation_Month[Name] = EARLIER(Utilisation_Month[Name]) &amp;&amp; Utilisation_Month[Week_Comm] = EARLIER(Utilisation_Month[Week_Comm]) ) ) &gt; 1, 0, // Multiple tasks in the same week, no increase in capacity IF( Utilisation_Month[Employee Start Date] &lt;= MAX(Utilisation_Month[Week_Comm]) &amp;&amp; (ISBLANK(Utilisation_Month[Employee End Date]) || Utilisation_Month[Employee End Date] &gt;= MAX(Utilisation_Month[Week_Comm])), MaxCapacityPerWeek - COUNTROWS( FILTER( Utilisation_Month, Utilisation_Month[Name] = EARLIER(Utilisation_Month[Name]) &amp;&amp; Utilisation_Month[Week_Comm] = EARLIER(Utilisation_Month[Week_Comm]) &amp;&amp; Utilisation_Month[Role] = EARLIER(Utilisation_Month[Role]) ) ), 0 // Employee not working in the specified week ) ) ) Sent from Outlook for AndroidVAR MaxCapacityPerWeek = 5RETURNSUMX(&nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; Utilisation_Month,&nbsp; &nbsp; &nbsp; &nbsp; Utilisation_Month[Week_Comm] = MAX(Utilisation_Month[Week_Comm]) // Adjust based on your calendar selection&nbsp; &nbsp; ),&nbsp; &nbsp; IF(&nbsp; &nbsp; &nbsp; &nbsp; COUNTROWS(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Utilisation_Month,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Utilisation_Month[Name] = EARLIER(Utilisation_Month[Name]) &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Utilisation_Month[Week_Comm] = EARLIER(Utilisation_Month[Week_Comm])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; ) &gt; 1,&nbsp; &nbsp; &nbsp; &nbsp; 0, // Multiple tasks in the same week, no increase in capacity&nbsp; &nbsp; &nbsp; &nbsp; IF(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Utilisation_Month[Employee Start Date] &lt;= MAX(Utilisation_Month[Week_Comm]) &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (ISBLANK(Utilisation_Month[Employee End Date]) || Utilisation_Month[Employee End Date] &gt;= MAX(Utilisation_Month[Week_Comm])),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; MaxCapacityPerWeek - COUNTROWS(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Utilisation_Month,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Utilisation_Month[Name] = EARLIER(Utilisation_Month[Name]) &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Utilisation_Month[Week_Comm] = EARLIER(Utilisation_Month[Week_Comm]) &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Utilisation_Month[Role] = EARLIER(Utilisation_Month[Role])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 // Employee not working in the specified week&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; ))&nbsp;

lbendlin · Answer

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

joynulali · Answer

Hi thanks for getting back to me, I managed to get this working by using multiple small dax queries to essentially create several columns to breakdown the query and achieve the final result. Thanks&nbsp;

Forum Discussion

Calculate remaining capacity

2 Replies

Related content

Days Remaining Calculation

Remaining Balance Depends on Prior Month

Calculate required Fabric Capacity

Fabric Capacity Cost

Days Remaining Calculation