Forum Discussion

robinlolo's avatar
robinlolo
New Member
5 years ago
Solved

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
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    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!

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      robinlolo
      New Member
      5 years ago

      Thank you. 

       

      Can you shed more light, please?


  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    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!