Get date from another table - no relationship

[robinlolo]

Hi Team 

I am new to Power BI and DAX, and not sure what would be the best approach to get the below results. Any help or ideas would be much appreciated. Thank you

Tabel1 - SalesTarget

SalesPersonID	MonthNum	Year	Target
1	1	2020	20000
2	1	2020	50000
1	2	2020	20000
2	2	2020	50000

Table 2 - Transactions

SalesPersonID	TransferMonthNum	TransferMonthYear	Actual [calculated field]
1	1	2020	5000
1	1	2020	2000
1	1	2020	3000
2	1	2020	5000
2	1	2020	2000
2	1	2020	3000

The end results should be 

SalesPersID	Month	Year	Actual	Target
1	1	2020	10000	20000
2	1	2020	10000	50000

[v-kelly-msft · Accepted solution]

Hi  @robinlolo ，

 Create 2 measures as below:

Actual = SUMX(FILTER(ALL(Transactions),'Transactions'[SalesPersonID]=MAX('Transactions'[SalesPersonID])&&'Transactions'[TransferMonthNum]=MAX('Transactions'[TransferMonthNum])),'Transactions'[Actual([calculated field]])])

_Target = LOOKUPVALUE('SalesTarget'[Target],'SalesTarget'[SalesPersonID],MAX('Transactions'[SalesPersonID]),'SalesTarget'[MonthNum],MAX('Transactions'[TransferMonthNum]))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-kelly-msft · Accepted solution]

Hi  @robinlolo ，

 Create 2 measures as below:

Actual = SUMX(FILTER(ALL(Transactions),'Transactions'[SalesPersonID]=MAX('Transactions'[SalesPersonID])&&'Transactions'[TransferMonthNum]=MAX('Transactions'[TransferMonthNum])),'Transactions'[Actual([calculated field]])])

_Target = LOOKUPVALUE('SalesTarget'[Target],'SalesTarget'[SalesPersonID],MAX('Transactions'[SalesPersonID]),'SalesTarget'[MonthNum],MAX('Transactions'[TransferMonthNum]))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@robinlolo , Have common dimensions for Sales Person and Month year.  Then you can use measures like these with common table

sum(Sales[Amount])

Sum(Transaction[Target])

[robinlolo]

Thank you. 

Can you shed more light, please?