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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
TanFouc87
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 IDCust IDTransaction Date
1A1/10/2023
2A6/2/2023
3A7/19/2023
4A10/14/2023
5B6/20/2023
6C2/14/2023
7C3/16/2023
8C9/7/2023
9D7/8/2023
10D8/8/2023
11D8/21/2023
12D9/2/2023
13D10/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 IDClosest Transaction IDClosest Transaction Date
A26/2/2023
B56/20/2023
C73/16/2023
D97/8/2023

 

How to do that? Thank you in advance!

1 ACCEPTED SOLUTION
FreemanZ
Super User
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:

FreemanZ_1-1699970402763.png

View solution in original post

2 REPLIES 2
FreemanZ
Super User
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:

FreemanZ_1-1699970402763.png

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

Top Solution Authors