Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
34 | |
14 | |
12 | |
11 | |
10 |