Forum Discussion
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 the Sales Date is between the Valid from and Valid to date.
What should i do to achieve this? can i build a filter to compare the sales date and valid from/to date, make 'True' if within the valid date and apply it to the visual?
Thank you so much.
SALES VOLUME TABLE
| CUSTOMER | SALE DATE |
| A | 2023-01-02 |
| A | 2023-01-21 |
| A | 2023-01-02 |
| A | 2023-09-02 |
SALES INCENTIVE TABLE
| CUSTOMER | STAFF | FROM DATE | TO DATE |
| A | PETER | 2023-01-01 | 2023-03-01 |
| B | TOM | 2023-01-01 | 2023-03-01 |
| C | DAVID | 2023-03-01 | 2023-06-01 |
Expected Result
| CUSTOMER | SALE DATE | STAFF |
| A | 2023-01-02 | PETER |
| A | 2023-01-21 | PETER |
| A | 2023-01-02 | PETER |
| A | 2023-09-02 |
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 )
6 Replies
speedrampsSuper User
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 )Thank you, it works.
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.
- speedrampsThanks 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.
Thank you.
- speedramps
Thank you for the kudos.
If you need more help then raise a new ticket and quote speedramps anywhere in the text, I will then receive an automatic notification and will be delighted to help you again.
Please always try provide example input data as table text (not a screen print) so we can import the data to build a solution for you. You will gain respect and a much quicker and better responses with the more effort put in to describing problems
