Forum Discussion
New Measure to Compare Date between Different Table
Click here to download the solution
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 )
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.
Super User