Forum Discussion

PowerBIUser9901's avatar
PowerBIUser9901
Advocate II
7 years ago
Solved

DateDiff with repeating Order ID's

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. 

Need Help
  • v-eachen-msft's avatar
    v-eachen-msft
    7 years ago

    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

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

  • v-eachen-msft's avatar
    v-eachen-msft
    Community Support
    7 years ago

    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's avatar
    Anonymous
    Not applicable
    7 years ago

    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