Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
gauravnarchal
Post Prodigy
Post Prodigy

DATEDIFF / Average Measure

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 DateReturn DateNumber of ItemsSpend
20-Jun-2010-Jul-20324500
25-Jun-2015-Jul-20127500
30-Jun-2020-Jul-20130500
5-Jul-2025-Jul-20133500
10-Jul-2030-Jul-20136500
15-Jul-204-Aug-20139500
20-Jul-209-Aug-20442500
25-Jul-2025-Jul-20545500
14-Aug-203-Sep-20648500
3-Sep-2023-Sep-20151500
23-Sep-2023-Oct-20254500
13-Oct-2012-Nov-20157500
2-Nov-2011-Jan-21860500
22-Nov-2031-Jan-21163500

 

Result

Dispatch DateReturn DateNumber of ItemsSpenddaysReturn Inventory days Average
20-Jun-2010-Jul-203     24,5002060408.33
25-Jun-2015-Jul-201     27,50020201375.00
30-Jun-2020-Jul-201     30,50020201525.00
5-Jul-2025-Jul-201     33,50020201675.00
10-Jul-2030-Jul-201     36,50020201825.00
15-Jul-204-Aug-201     39,50020201975.00
20-Jul-209-Aug-204     42,5002080531.25
25-Jul-2026-Jul-205     45,500159100.00
14-Aug-203-Sep-206     48,50020120404.17
3-Sep-2023-Sep-201     51,50020202575.00
23-Sep-2023-Oct-202     54,5003060908.33
13-Oct-2012-Nov-201     57,50030301916.67
2-Nov-2011-Jan-218     60,50070560108.04
22-Nov-2031-Jan-211     63,5007070907.14
 Total36   616,0003811105557.47
1 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] )

image.png

 

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.

View solution in original post

3 REPLIES 3
Vera_33
Resident Rockstar
Resident Rockstar

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

 

Vera_33_2-1624409232207.png

 

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

Vera_33_3-1624409296412.png

 

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] )

image.png

 

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.