Forum Discussion
Find the closest date before from a certain date
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!
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 _resultit worked like:
2 Replies
FreemanZSuper User
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 _resultit worked like: