Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dear All
I need help to create the below three measures
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 |
Solved! Go to 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.
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 @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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |