Find the closest date before from a certain date

[Anonymous]

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!

[FreemanZ · Accepted solution]

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:

[Anonymous]

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