cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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.

Approval_TimeDAX.png

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: DateDiff with repeating Order ID's

Hi @PowerBIUser9901 ,

 

You need to create a new measure:

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

5-1.PNG

 

Best Regards,

Eads

 

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

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

3 REPLIES 3
Highlighted
Helper I
Helper I

Re: DateDiff with repeating Order ID's

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

Highlighted
Super User I
Super User I

Re: DateDiff with repeating Order ID's

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:

Capture11.PNG

 

Let me know if this works.

 

Thanks,

Tejaswi

 

Highlighted
Community Support
Community Support

Re: DateDiff with repeating Order ID's

Hi @PowerBIUser9901 ,

 

You need to create a new measure:

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

5-1.PNG

 

Best Regards,

Eads

 

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

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors