topic New Measure to Compare Date between Different Table in DAX Commands and Tips

New Measure to Compare Date between Different Table

tomcch — Wed, 01 Nov 2023 14:22:26 GMT

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

Re: New Measure to Compare Date between Different Table

speedramps — Wed, 01 Nov 2023 22:26:48 GMT

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 )

Re: New Measure to Compare Date between Different Table

Anonymous — Thu, 02 Nov 2023 06:41:17 GMT

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.

Re: New Measure to Compare Date between Different Table

speedramps — Thu, 02 Nov 2023 07:33:54 GMT

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.

Re: New Measure to Compare Date between Different Table

tomcch — Thu, 02 Nov 2023 12:35:30 GMT

Thank you, it works.

Re: New Measure to Compare Date between Different Table

tomcch — Thu, 02 Nov 2023 12:36:07 GMT

Thank you.

Re: New Measure to Compare Date between Different Table

speedramps — Fri, 03 Nov 2023 12:27:40 GMT

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