Forum Discussion
Measure from two tables
Not sure how to share my pbix file, or for that matter how to attach anything
If this helps: Here is some sample data as an example
Task Table:
| Task | Project | Window |
| a | p1 | 12 |
| b | p1 | 20 |
| c | p1 | 15 |
| d | p2 | 35 |
| e | p2 | 42 |
| f | p3 | 18 |
| g | p3 | 8 |
| h | p3 | 23 |
| i | p3 | 21 |
| j | p4 | 14
|
People table
| Person | Task | LastDateWorked |
| 1 | a | 5/9/2020 |
| 1 | b | 4/24/2020 |
| 1 | c | 5/3/2020 |
| 1 | d | 5/9/2020 |
| 1 | e | 5/7/2020 |
| 2 | f | 4/24/2020 |
| 2 | g | 5/4/2020 |
| 2 | h | 4/29/2020 |
| 2 | i | 5/18/2020 |
| 2 | j | 5/19/2020 |
| 3 | a | 5/4/2020 |
| 3 | b | 5/9/2020 |
| 4 | c | 5/9/2020 |
| 4 | d | 4/21/2020 |
| 4 | e | 5/18/2020 |
| 4 | f | 5/10/2020 |
| 4 | g | 5/7/2020 |
| 5 | h | 4/21/2020 |
| 5 | i | 5/5/2020 |
| 5 | j | 5/11/2020 |
So for task "A" with a 12 day window. Using 5/18/2020 as today, 12 days ago is 5/6. Person 1 worked 5/9 so he/she is inside the window (good) while person 3 is outside the window (Bad). Two people work, one good, so 50% for that task.
I would use the same logic when doing it by project, or doing it by person.
2 tables, 1 measure...
So, you might have some luck looking at Open Tickets. It's designed to deal with date intervals. https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364#M147
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.I'm new enough to Power BI to say I really don't have a clue as to how to convert the code in the example you provided to work on my data, so don't know if it would work.
Ashish,
Thanks. It's a great start I think. It doesn't report task g as 0% and when I look at it by project rather than task it doesn't calculate correctly. However, I'm hoping this gives me enough of an idea to do what I need. I'll let you know.
