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

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.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

I just sent you a PM with my email address

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