Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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!
Solved! Go to Solution.
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:
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:
That's great. Thank you a lot. Many blessing to you!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
24 | |
21 | |
18 | |
14 | |
11 |
User | Count |
---|---|
44 | |
35 | |
25 | |
22 | |
22 |