Summing based on criteria from another table?

[Anonymous]

I have a date table that has a row for each employee and another table with due dates by employee. I would like to compare each date/ employee row to the due dates table and sum the effort value for each due date for that employee that is greater than or equal the date being looked up. I'm having trouble trying to find the correct way to set this up in query editor. I have included pictures of the desired outcome below.

Inputs:

Due Date Table

Date Table

Outcome:

Outcome     

Thanks!

[Anonymous]

@Anonymous - Could you post your pbix? I think you made this more complicated than it needs to be and I'd be happy to see whether it can be refactored.

Cheers!

Nathan

[Anonymous]

I sent you a pm regarding this, since there is privledged info in the pbix

[Anonymous · Accepted solution]

Ok so I figured this out after doing some research. Going to post my solution here because I hate going to forums and seeing "nvm figured it out - posted Nov. 2009".

I modified the code from this post.

Shoutout to @Greg_Deckler  for figuring this out.

My Solution:

1. Combine the calendar table with a unique employee name table (not pictured in my original question, it's just a list of distinct employee names)
2. Add on effort to this table, matching the due date and employee combination to the respective row in the combined employee calendar table.
3. Create a custom column with the following code:

Remaining Effort =

// Get the current day and employee for the current row of the calendar table

VAR __day = 'Employee Calendar Combined'[date]

VAR _employee = 'Employee Calendar Combined'[employee name]

// Create Table Filtered by current employee

VAR __table1 = FILTER('Employee Calendar Combined','Employee Calendar Combined'[employee name]=_employee)

// Create a table of all due dates greater than the current day

VAR _table2 = FILTER(__table1,[Due Date Fact Table.Due Date]>=__day)

VAR _total = sumx(_table2,[Due Date Fact Table.Effort])

return

_total

The Result:This column measure allows me to forecast how busy an employee is based on the scheduled due dates and the amount of effort for each project. I also created a measure that accounts for progression, adjusting the value of the remaining effort.

Employee Workload Calendar

Thanks for coming to my Ted Talk

[Ashish_Mathur]

Hi,

Do you want a calculated column or a measure solution?

[Anonymous]

Hi,

I think a calcualted column would work best

[Ashish_Mathur]

Hi,

Share the two tables in a format that can be pasted in an Excel file.

[Anonymous]

@Anonymous  - 

You could do something like this:

Your Measure = 
var date_to_assess = MAX('Date'[Date])
return CALCULATE(SUM(Effort),'Date'[Date] >= date_to_assess)

Hope this helps,

Nathan

[Anonymous]

Hi Nathan, 

I think this is on the right track, but I need to do this at a column level for each unique Employee/Date pair not just the most recent date.

[Anonymous]

@Anonymous  - I picture the following model:

1. A dimension table for date - it would not include the employee.

2. A dimension table called employee or something like that.

3. The Due Table is a fact table.

4. You can join the 3 tables together, so that the selections in the 2 dimension tables filter the fact table.

Then, in a table visual, add the employee column from the employee dimension, the date from the date dimension, and the Measure that I specified. It should give the results you're looking for.

Cheers!

Nathan