Measure from two tables

[GarnseyG]

I have one table containing the names of different tasks and a field called Window that define a number of days. Table is call Tasks and has three elements, Task, Job, Window.

A second table identifies the people who have worked on each task and the last time they performed work on that task. Table is called AssignedPeople and has elements Person, Task, LastDateWorked

I want to be able to calculate what percent of the people have worked on each task since Today - window.

I will also want to be able to calculate the same thing, but a job level (tasks are assigned to jobs).

I can't figure out how to do this, Your help would be most appreciated.

[v-alq-msft]

Hi, @GarnseyG 

I'd like to suggest you create a measure as below.

Percenatge By task = 
var _tabperson = 
SUMMARIZE(
    People,
    People[Person],
    People[Task],
    People[LastDateWorked],
    "Window",
    var _task = People[Task]
    return
    LOOKUPVALUE(Task[Window],Task[Task],_task)
)
var _newtab = 
ADDCOLUMNS(
    _tabperson,
    "flag",
    IF(
        TODAY()-[Window]<=[LastDateWorked],
        1,0
    )
)
var numofgood = 
SUMX(
    FILTER(
        _newtab,
        [flag]=1
    ),
    [flag]
)

var result = 
numofgood/DISTINCTCOUNT(People[Person])
return
IF(
    ISBLANK(result),
    0,
    result
)

Today is 5/21/2020. Here is the result.

The pbix is attached in the end.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GarnseyG]

Whow, most impressive! It's going to take some time to understand what you've done here, but the calculations look right at the task level. I'm going to see if I can figure out what you did and apply it to do it at the project level.

I'll let you know.

[amitchandak]

@GarnseyG , you should able to join both tables on task and should able to calculate.

For more help Can you share sample data and sample output.

[GarnseyG]

Not sure how to share my pbix file, or for that matter how to attach anything

[GarnseyG]

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.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[GarnseyG]

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.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[GarnseyG]

This works for the individual tasks, but not for projects. Three of the four have the same percentages for each task (projects P1, P2, and P4) so the project level numbers are correct I think only because of this. But project P3 is wrong. There are 8 people who have reported time (yes some are the same person in different tasks but that doesn't matter) and 4 have time within the window. Project p3's percent should be 50%.

In total, 20 people (again the fact it was the same person in different tasks is not important) reported time and in 13 cases they were in the window, so 65%.

Hopefully this better explains it all. I do very much appreciate your working on this!!!

[Greg_Deckler]

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.

[GarnseyG]

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.