Power BI DAX

[Amz_123]

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.
Thanks in advance,
Amz

[Mariusz · Accepted solution]

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.