Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Regular Visitor

## 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!

1 ACCEPTED SOLUTION
Super User

hi @TanFouc87 ,

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:

2 REPLIES 2
Super User

hi @TanFouc87 ,

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:

Regular Visitor

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

## Helpful resources

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors