topic Find the closest date before from a certain date in DAX Commands and Tips

Find the closest date before from a certain date

Anonymous — Tue, 14 Nov 2023 11:14:38 GMT

Hi everyone,
I am having an issue needed to solve. I have a transaction data like this:

Transaction ID	Cust ID	Transaction Date
1	A	1/10/2023
2	A	6/2/2023
3	A	7/19/2023
4	A	10/14/2023
5	B	6/20/2023
6	C	2/14/2023
7	C	3/16/2023
8	C	9/7/2023
9	D	7/8/2023
10	D	8/8/2023
11	D	8/21/2023
12	D	9/2/2023
13	D	10/9/2023

I would like to create a matrix where x axis is cust ID and a slicer to manually choose a date from date_ref table. It is required that the matrix would display the "closest before" transaction date of each Cust ID. For example, If I choose 7/9/2023 from slicer. The output should be:

Cust ID	Closest Transaction ID	Closest Transaction Date
A	2	6/2/2023
B	5	6/20/2023
C	7	3/16/2023
D	9	7/8/2023

How to do that? Thank you in advance!

Re: Find the closest date before from a certain date

FreemanZ — Tue, 14 Nov 2023 14:00:23 GMT

hi Anonymous ,

try to

1) plot a slicer with a calculated table like:

date_ref = CALENDAR(MIN(data[Transaction Date]), MAX(data[Transaction Date]))

2) plot a table visual with customer id column and two measures like:

Closest Transaction Date = MAXX( FILTER( data, data[Transaction Date]<SELECTEDVALUE(date_ref[date]) ), data[Transaction Date] )

Closest Transaction ID = VAR _date = [Closest Transaction Date] VAR _result = MAXX( FILTER( data, data[Transaction Date] = _date ), data[Transaction ID] ) RETURN _result

it worked like:

Re: Find the closest date before from a certain date

Anonymous — Wed, 15 Nov 2023 02:30:25 GMT

That's great. Thank you a lot. Many blessing to you!