Forum Discussion

tomcch's avatar
tomcch
Frequent Visitor
2 years ago
Solved

New Measure to Compare Date between Different Table

Hello all,   I have a main Sales volume table and a Sales incentive table. I built a relationship by "Customer" between two table. Hope to get the Sales name (STAFF) from the incentive table if t...
  • speedramps's avatar
    speedramps
    2 years ago

    Click here to download the solution

    Download PBIX 

    Thanks for the clear description of the problem with example data. I wish everyone did that!

    Remember we are unpaid volunteers. So please click the thumbs up and the [accept as solution] button to leave kudos. 

    One question per ticket please. If you need to extend your request then please raise a new ticket.

    You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

    If you quote speedramps in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.

    Please now click the thumbs up and the [accept as solution] button.  Thnak you.


    How it works ....
    This measure will return all valid staff (just in case there is  ore than one)

    Valid staff = 
VAR customer = SELECTEDVALUE('SALES VOLUME'[CUSTOMER])
VAR saledate = SELECTEDVALUE('SALES VOLUME'[SALE DATE] )
VAR mysubset = 
FILTER('SALES INCENTIVE', 
'SALES INCENTIVE'[CUSTOMER] = customer  
 && 'SALES INCENTIVE'[FROM DATE] <= saledate 
&& 'SALES INCENTIVE'[TO DATE] >= saledate 
)
RETURN
CALCULATE(
CONCATENATEX('SALES INCENTIVE','SALES INCENTIVE'[STAFF],", "),
 mysubset )



     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi tomcch ,
The following DAX might help with you:

STAFF NAME = 
CALCULATE(
    MAX('SALES INCENTIVE TABLE'[STAFF]),
    FILTER(
        'SALES INCENTIVE TABLE',
        'SALES VOLUME TABLE'[CUSTOMER] = 'SALES INCENTIVE TABLE'[CUSTOMER] &&
        'SALES VOLUME TABLE'[SALE DATE] >= 'SALES INCENTIVE TABLE'[FROM DATE] &&
        'SALES VOLUME TABLE'[SALE DATE] <= 'SALES INCENTIVE TABLE'[TO DATE]
    )
)

You can use the DAX to create a new column.
And the final output is shown in the following figure:

Best Regards,

Dino Tao

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

 

 

 

  • speedramps's avatar
    speedramps
    Icon for Super User rankSuper User
    2 years ago
    Thanks for helping but it is impolite to ask for kudos when I have already answered the question yesterday.
     
    Incidentally, your answer contains a bug. 
     
    If there are multiple valid staff for the date then it uses MAX to  alphabetically display just the last one.
     
    See my solution which uses CONCATENATEX, which can display multiple valid staff for the date.
     
     
  • tomcch's avatar
    tomcch
    Frequent Visitor
    2 years ago

    Thank you.