cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent 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:

Frequent Visitor

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors