cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
robinlolo
New Member

Get date from another table - no relationship

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

SalesPersonIDMonthNumYearTarget
11202020000
21202050000
12202020000
22202050000

 

Table 2 - Transactions

SalesPersonIDTransferMonthNumTransferMonthYearActual
[calculated field]
1120205000
1120202000
1120203000
2120205000
2120202000
2120203000

 

The end results should be 

SalesPersIDMonthYearActualTarget
1120201000020000
2120201000050000
4 REPLIES 4
Ashish_Mathur
Super User III
Super User III

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-kelly-msft
Community Support
Community Support

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:

 

vkellymsft_0-1627265005254.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

amitchandak
Super User IV
Super User IV

@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])

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Thank you. 

 

Can you shed more light, please?


Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors