Forum Discussion
Add a filter to a variable in a measure
Still trying to solve this:
I have the current measure which is calculating the capacity allocated (but incorrectly adding up each staff members capacity for each day in a month eg:
0.5 for each day in a 31 day month is being mutlplied as 0.5 x 31 = 15.5, whereas it should be 0.5 for the month.
Capacity allocated =
var a = SUMMARIZECOLUMNS('Resourcing'[Name],'Resourcing'[Start Date],'Resourcing'[End Date],'Resourcing'[Capacity],'dimDates'[Date])
var b = ADDCOLUMNS(a,"sm",if([Start Date]<=[Date] && [Date]<=[End Date],[Capacity]))
return sumx(b,[sm])
I have 2 options it seems now:
1. rewrite the measure so it calculates correctly
2. use NETWORKDAYS to divide one of the variables in the above measure (or some other way) to get the correct capacity allocated.
In theory, number 2 works, but not sure a) if its possible and b) if it is the best way to calculate the capacity allocated for any time measure for however many staff have capacity allocated.
I have the NETWORKDAYS working outside of the above measure, but using dates from a different table (as the Resourcing table is pivoted) and applying on data outside of the resourcing table, not sure if it will apply to the start and end dates in the Resourcing table.
Working Days = Var Start_Date = SELECTEDVALUE('Work Tracker'[Start Date])
var End_Date = SELECTEDVALUE('Work Tracker'[End Date])
RETURN
NETWORKDAYS(Start_Date, End_Date)Zubair_Muhammad - not sure if you could help with this one- I saw you had a solution to a related problem here: Solved: Using a Measure as a filter in DAX formula - Microsoft Fabric Community
Thanks