Forum Discussion
DATEDIFF / Average Measure
Dear All
I need help to create the below three measures
- Days = Return date – Dispatch date = Days
- Return Inventory days = Days * Number of Items
- 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 |
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,
WinnizIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
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])Hi Anonymous
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
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,
WinnizIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
