DateDiff with repeating Order ID's

[PowerBIUser9901]

Hi, I’m looking to calculate the difference between two dates in Power BI. I’m aware this is possible with DATEDIFF in DAX however the data I have contains repeating Order ID’s for each Product associated to it. See screenshot below.

How do I obtain an accurate calculation without recounting the same Order ID’s? Note: The distinct Order ID start and end dates do not change regardless how many products are associated to the ID.

Currently I have:

Approval_Time = DATEDIFF(Sheet2[Order_Date], Sheet2[Ship_Date].[Date], DAY)

I need to create a visual that is the average of the Approval_Time calculation.

Currently I have:

Average Approval Time in days = AVERAGE(Sheet2[Approval_Time])

However, the issue is that this Average is calculating the repeats. I appreciate any help and guidance with this calculation. 

[v-eachen-msft · Accepted solution]

Hi @PowerBIUser9901 ,

You need to create a new measure:

NetAvg =
AVERAGEX (
    VALUES ( 'Table'[Order_ID] ),
    CALCULATE ( AVERAGE ( 'Table'[Column] ) )
)

Best Regards,

Eads

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

[Anonymous]

Hi @PowerBIUser9901 ,

Follow these steps:

Add this calculated new table:

Table 3 = 
    SUMMARIZECOLUMNS(
        'Date diff'[Order ID] ,
        "OD",Max('Date diff'[Order Date ]),
        "SD",MAx('Date diff'[Ship Date]),
       
        "Date Diff" , DATEDIFF(Max('Date diff'[Order Date ]),Max('Date diff'[Ship Date]), DAY)
        )

Create a relationship between your existing Table and a new Table by linking the Order ID.

now put the Fields in the Table Visual:

My Output:

Let me know if this works.

Thanks,

Tejaswi

[Anonymous]

I think the easiest route would be to duplicate your table and use the 'remove duplicates' feature in Power Query.