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.

Reply
TanFouc87
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 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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors