DATEDIFF / Average Measure

[gauravnarchal]

Dear All

I need help to create the below three measures

1. Days = Return date – Dispatch date = Days
2. Return Inventory days =  Days * Number of Items
3. Average = Divide(Spend /  Return Inventory days)

Also, can I use the Average measure in the KPI card to show the average spend that is returned?

Data

Table Name - Return Inventory

Dispatch Date	Return Date	Number of Items	Spend
20-Jun-20	10-Jul-20	3	24500
25-Jun-20	15-Jul-20	1	27500
30-Jun-20	20-Jul-20	1	30500
5-Jul-20	25-Jul-20	1	33500
10-Jul-20	30-Jul-20	1	36500
15-Jul-20	4-Aug-20	1	39500
20-Jul-20	9-Aug-20	4	42500
25-Jul-20	25-Jul-20	5	45500
14-Aug-20	3-Sep-20	6	48500
3-Sep-20	23-Sep-20	1	51500
23-Sep-20	23-Oct-20	2	54500
13-Oct-20	12-Nov-20	1	57500
2-Nov-20	11-Jan-21	8	60500
22-Nov-20	31-Jan-21	1	63500

Result

Dispatch Date	Return Date	Number of Items	Spend	days	Return Inventory days	Average
20-Jun-20	10-Jul-20	3	24,500	20	60	408.33
25-Jun-20	15-Jul-20	1	27,500	20	20	1375.00
30-Jun-20	20-Jul-20	1	30,500	20	20	1525.00
5-Jul-20	25-Jul-20	1	33,500	20	20	1675.00
10-Jul-20	30-Jul-20	1	36,500	20	20	1825.00
15-Jul-20	4-Aug-20	1	39,500	20	20	1975.00
20-Jul-20	9-Aug-20	4	42,500	20	80	531.25
25-Jul-20	26-Jul-20	5	45,500	1	5	9100.00
14-Aug-20	3-Sep-20	6	48,500	20	120	404.17
3-Sep-20	23-Sep-20	1	51,500	20	20	2575.00
23-Sep-20	23-Oct-20	2	54,500	30	60	908.33
13-Oct-20	12-Nov-20	1	57,500	30	30	1916.67
2-Nov-20	11-Jan-21	8	60,500	70	560	108.04
22-Nov-20	31-Jan-21	1	63,500	70	70	907.14
	Total	36	616,000	381	1105	557.47

[Vera_33]

Hi @gauravnarchal 

Is your sample data table a table or a visual table? So the [Spend] is a column or a measure? There is one date different in Data and Result, 25 July 2020, so if it returns on the same day, you change different from 0 to 1?

I added calculated columns first for the return days

days = 
VAR diff =DATEDIFF('Table'[Dispatch Date],'Table'[Return Date],DAY)
RETURN
IF(diff=0,1,diff)


Return Inventory days = 
'Table'[days]*'Table'[Number of Items]

Then a measure for avg

AVG = SUM('Table'[Spend])/SUM('Table'[Return Inventory days])

[gauravnarchal]

Hi @Vera_33 

The data I shared is in the table.  Is there a way I can create a measure instead of the calculated column?

I have attached the sample PBIX if that helps.  Click Here

Thanks

Gaurav

[v-kkf-msft · Accepted solution]

Hi @gauravnarchal ,

Try the following measures:

diff = 
DATEDIFF( 
    MAX(ReturnInventory[Dispatch Date]), 
    MAX(ReturnInventory[Return Date]), 
    DAY 
)

Days = 
IF( 
    ISFILTERED(ReturnInventory[Dispatch Date]), 
    [diff], 
    SUMX( ALLSELECTED(ReturnInventory[Dispatch Date]), [diff] )
)

Inventory_days = [Days] * MAX(ReturnInventory[Number of Items])

Return Inventory days = 
IF(
    ISFILTERED(ReturnInventory[Dispatch Date]),
    [Inventory_days],
    SUMX( ALLSELECTED(ReturnInventory[Dispatch Date]), [Inventory_days] )
)

Average = DIVIDE( SUM(ReturnInventory[Spend]), [Return Inventory days] )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.