cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Power BI DAX

Hi Community,
Assist me in finding DAX to find Frequency of Item Returned back in a store.

My Data Looks like this:

In my Case, I have to find at what frequency an item is returned back to store.
Status :
Coming into store - Item getting into Store for sale
Sold - Item is sold to customer

Type:

Return: The item is returned back by customer to store due to dissatisfication.

Date:
Tells about at what date, Item is getting into store, sold and Returned back.

REQUIREMENT:
After the item is sold to customer, I have to find at what average interval the item is being returned back.
For A1: [ Sold and Return is taken into consideration]

=[ (jan 4 - jan 3) + (jan 6 - jan 3) + (jan 6 - jan 5) + (jan 9 - jan 3) + (jan 9 - jan 5) + (jan 9 - jan 7)] / [6]

= (1+3+1+6+4+2) / 6 = 2.8 (Required Answer)
Here X- Axis is my Item and Value is Frequency.

Meaning:
1. (jan 4 - jan 3) : Sold at jan 3 and returned at jan 4
2. (jan 6 - jan 3) + (jan 6 - jan 5):  Item returned at jan 6 may be from item sold at jan 3 or at jan 5. so, (jan 6 - jan 3) and (jan 6 - jan 5).
3. (jan 9 - jan 3) + (jan 9 - jan 5) + (jan 9 - jan 7): Item returned at jan 9 may be from item sold at jan 3 or at jan 5 or at jan 7. so, (jan 9 - jan 3) and (jan 9 - jan 5) and (jan 9 - jan 7).

Appreciate any guidance.
Amz

1 ACCEPTED SOLUTION
Community Champion

Hi @Amz_123

You can try Measure like this.

``````Measure =
VAR __allexcept = ALLEXCEPT( 'Table', 'Table'[Item] )
VAR __sold = CALCULATETABLE( VALUES( 'Table'[Date] ), 'Table'[Status] = "Sold", __allexcept )
VAR __tbl =
GENERATE(
__sold,
VAR __date = 'Table'[Date]
RETURN
CALCULATETABLE(
SELECTCOLUMNS(
'Table',
"@Date", 'Table'[Date]
),
'Table'[Type] = "Return",
'Table'[Date] > __date,
__allexcept
)
)
RETURN
SUMX( __tbl, INT(  [@Date] - 'Table'[Date] ) ) / SUMX( __tbl, 1 )``````

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Community Champion

Hi @Amz_123

You can try Measure like this.

``````Measure =
VAR __allexcept = ALLEXCEPT( 'Table', 'Table'[Item] )
VAR __sold = CALCULATETABLE( VALUES( 'Table'[Date] ), 'Table'[Status] = "Sold", __allexcept )
VAR __tbl =
GENERATE(
__sold,
VAR __date = 'Table'[Date]
RETURN
CALCULATETABLE(
SELECTCOLUMNS(
'Table',
"@Date", 'Table'[Date]
),
'Table'[Type] = "Return",
'Table'[Date] > __date,
__allexcept
)
)
RETURN
SUMX( __tbl, INT(  [@Date] - 'Table'[Date] ) ) / SUMX( __tbl, 1 )``````

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.