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.
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.
Solved! Go to 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.
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.
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
I think the easiest route would be to duplicate your table and use the 'remove duplicates' feature in Power Query.
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |