$value from one table to another

[corange]

Hi everyone,

I am stuck with a probleme. The outcome I am trying to reach is to have the cumulative earnings YTD against the accumulated earnings target. The graph should update when changing the employee categories as each category as its own target.

I have three table:

The first one list all job completed with employee ID and earnings through out the year. there are also a column CODE formatted as PERIOD DATE STATE EMPLOYEE ID e.g. 20180715WA4567

The second table lists all employee using employees, their details including name and their category (welder, accountants). We also have CODE in this table. An employee ID can be recycled over the year but we want to keep the historical data hence the CODE

The third table list employees categories and their earnings target for each period of the financial year.

The first table is linked to table 2 using the CODE, table 2 is linked to table 3 using employee categories. I was wondering if it was possible to search for all similar code in table 1 and the associated earnings and sum it in a colum in table 2. At the minute, because table 1 and 3 is not directly related, I obtain a flat line in terms of cumulative earnings while it should go increasingly as for the target.

I hope I make sense. Thank you

[amitchandak]

Try Bi-directional join for passing filters across.

For YTD try

Year  = CALCULATE(SUM(table[column]),DATESYTD('Date'[Date Filer]))
Last Year  = CALCULATE(SUM(table[column]),DATESYTD(dateadd('Date'[Date Filer],-12,MONTH)))


Year Sales = CALCULATE(TOTALYTD(sum(Sales[Sales Amount]),('Date'[Date Filer])))
Lasr Year Sales = CALCULATE(TOTALYTD(sum(Sales[Sales Amount]),dateadd('Date'[Date Filer].-12,month)))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[corange]

 Hi,

I am really new to POWER BI and trying this didnt work for me.

In table 1 I have got a same CODE multiple time with a dollar value associated to each. 

I would like in table 2 for BI to look for that same CODE and to add against that CODE the total value it obtained by sum up all the SAME CODE and their value from table 1.

I have tried using the RELATED () but it will not let me select the table I am after which one.

Any tips?  

[v-xuding-msft]

Hi @corange ,

Can you please share some sample data and your expected results? Then we will understand your requirement clearly.

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

Best Regards,

Xue Ding

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

[corange]

In addition to the above. I have looked into merge query but it is a quite significant amount of data.