Forum Discussion

Aylanna's avatar
Aylanna
Frequent Visitor
2 years ago
Solved

Sum of Partially Filtered Data (Highly Complex)

I am trying to get a measure to calculate properly, and for the most part, it works precisely the way it should.  The measure is:   % Capacity - DIVIDE(SUM(TashData[Hours]),Sum(HCOrgData[Hours/Mont...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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?

  • Aylanna's avatar
    Aylanna
    2 years ago

    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's avatar
Aylanna
Frequent Visitor
2 years ago

Great!  Thank you.  You should have a link in your email with the PBIX file.

Aylanna's avatar
Aylanna
Frequent Visitor
2 years ago

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]))