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 Sale...
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!

  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Hi,

    You may download my PBI file from here.

    Hope this helps.

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?