Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I want to create a measure that calculates the number of days between a customer buys a product and returns it (if that happens).
Our sales are stored in rows in a table like the one below:
Every bought product is stored in individual rows and has an unique purchase ID. Can anyone help to count the number of days between the pruchase date and the return date?
Best,
NicoM96
Solved! Go to Solution.
Hi @Anonymous ,
According to your description, here's my solution.
Create a measure.
Measure =
IF (
MAX ( 'Table'[Purchase/return] ) = "return",
DATEDIFF (
MAXX (
FILTER (
ALL ( 'Table' ),
'Table'[Product] = MAX ( 'Table'[Product] )
&& 'Table'[Purchase ID] = MAX ( 'Table'[Purchase ID] )
&& 'Table'[Date] <= MAX ( 'Table'[Date] )
&& 'Table'[Purchase/return] = "Purchase"
),
'Table'[Date]
),
MAX ( 'Table'[Date] ),
DAY
),
BLANK ()
)
Get the expected result.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
According to your description, here's my solution.
Create a measure.
Measure =
IF (
MAX ( 'Table'[Purchase/return] ) = "return",
DATEDIFF (
MAXX (
FILTER (
ALL ( 'Table' ),
'Table'[Product] = MAX ( 'Table'[Product] )
&& 'Table'[Purchase ID] = MAX ( 'Table'[Purchase ID] )
&& 'Table'[Date] <= MAX ( 'Table'[Date] )
&& 'Table'[Purchase/return] = "Purchase"
),
'Table'[Date]
),
MAX ( 'Table'[Date] ),
DAY
),
BLANK ()
)
Get the expected result.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi:
If you want to see positive value for answer it would be:
Hi Nico:
I was calling your table "Purchases.
I added Calc col
I hope you can mark as solved. Thanks..
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |