October 28 & 29: Experts share their secrets on how to pass the Fabric Analytics Engineer certification exam—live. Learn more
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
Solved! Go to 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 )
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 )
User | Count |
---|---|
105 | |
99 | |
98 | |
86 | |
49 |
User | Count |
---|---|
162 | |
142 | |
132 | |
102 | |
63 |