Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Outcome:
Thanks!
Solved! Go to 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:
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.
Thanks for coming to my Ted Talk
@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
I sent you a pm regarding this, since there is privledged info in the pbix
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:
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.
Thanks for coming to my Ted Talk
Hi,
Do you want a calculated column or a measure solution?
Hi,
I think a calcualted column would work best
Hi,
Share the two tables in a format that can be pasted in an Excel file.
@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
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 - 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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |