Forum Discussion
Sum of Partially Filtered Data (Highly Complex)
Aylanna I'd have to see the data tables to form a stronger opinion on an approach, but within a measure, can you save in a variable the number of months the employee worked in Org A and use that as the number of months of hours divided by the months of target hours?
I got it!!!!
Thanks to all your work, plus a little fine tuning from this vid ... DAX and the Start Date End Date Problem aka Events In Progress (youtube.com)Here is how I have the measure written:
% Capacity =VAR OrgStartDate = FIRSTDATE(HCOrgData[Month])VAR OrgEndDate = LASTDATE(HCOrgData[Month])VAR OrgPeriodHours = CALCULATE(SUM(TASHData[Hours]),TASHData[Timeframe] >= OrgStartDate, TASHData[Timeframe]<=OrgEndDate)Return DIVIDE(OrgPeriodHours, SUM(HCOrgData[Hours/Month]))
Aylanna I'd have to see the data tables to form a stronger opinion on an approach, but within a measure, can you save in a variable the number of months the employee worked in Org A and use that as the number of months of hours divided by the months of target hours?
I could certainly give it a try! While I can do a decent amount of formula writing, and can program macros, I'm not super familiar with variables and use in DAX.
What would you want to see in the data tables?
Aylanna All the tables you're using as the source of the calculation, and the viz you're creating with it. Can you redact information and share?
Sure! Is there an email that I should use for the access? I want to restrict the link. I redacted out as much as I could and still get you working data.