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 all,
I am strugling to create calculation based on unique values from a table.
I need to calculate average duration using only unique ID taking earliest and oldest timestamps.
One ID should only have one create and close in table to have correct calculation. Please see table 3 for what i need:
WHat i started with:
table 1
ID | Closed | Created | Duration |
1 | 05/09/2019 | 14/09/2019 | 9 |
2 | 07/09/2019 | 15/09/2019 | 8 |
3 | 10/09/2019 | 13/09/2019 | 3 |
1 | 08/09/2019 | 15/09/2019 | 7 |
2 | 08/09/2019 | 14/09/2019 | 6 |
1 | 05/09/2019 | 13/09/2019 | 8 |
4 | 02/09/2019 | 14/09/2019 | 12 |
Then i managed to create:
table 3
ID | Closed | Created | Duration | Earliest | Oldest | Duration per case |
1 | 05/09/2019 | 14/09/2019 | 9 | 05/09/2019 | 15/09/2019 | 10 |
2 | 07/09/2019 | 15/09/2019 | 8 | 07/09/2019 | 15/09/2019 | 8 |
3 | 10/09/2019 | 13/09/2019 | 3 | 10/09/2019 | 13/09/2019 | 3 |
1 | 08/09/2019 | 15/09/2019 | 7 | 05/09/2019 | 15/09/2019 | 10 |
2 | 08/09/2019 | 14/09/2019 | 6 | 07/09/2019 | 15/09/2019 | 8 |
1 | 05/09/2019 | 13/09/2019 | 8 | 05/09/2019 | 15/09/2019 | 10 |
4 | 02/09/2019 | 14/09/2019 | 12 | 02/09/2019 | 13/09/2019 | 11 |
And then i already cteated new table with unique values (using VALUES()). Not sure if this is the best way, but the one i have so far. What i need in the end is:
table 3
ID | Earliest | Oldest | Duration per case |
1 | 05/09/2019 | 15/09/2019 | 10 |
2 | 07/09/2019 | 15/09/2019 | 8 |
3 | 10/09/2019 | 13/09/2019 | 3 |
4 | 02/09/2019 | 13/09/2019 | 11 |
So my Average is 8 insteaed 8.7
Please help with methodology
Regards
Filarap
Solved! Go to Solution.
Hi @filarap ,
By my test, the oldest date of ID 4 should be 14/09/2019. Are you sure that the output is right (13/09/2019 )? If the date is 14/09/2019, the average value is 8.7. The following is my formulas. And I attached my sample that you can reference.
Calculated column:
Earliest = CALCULATE(MIN('Table'[Closed]),ALLEXCEPT('Table','Table'[ID])) Oldest = CALCULATE(MAX('Table'[Created]),ALLEXCEPT('Table','Table'[ID])) Duration per case = DATEDIFF('Table'[Earliest],'Table'[Oldest],DAY)
AVG = AVERAGE('Table'[Duration per case])
Measure:
Average = AVERAGE('Table'[Duration per case])
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @filarap ,
By my test, the oldest date of ID 4 should be 14/09/2019. Are you sure that the output is right (13/09/2019 )? If the date is 14/09/2019, the average value is 8.7. The following is my formulas. And I attached my sample that you can reference.
Calculated column:
Earliest = CALCULATE(MIN('Table'[Closed]),ALLEXCEPT('Table','Table'[ID])) Oldest = CALCULATE(MAX('Table'[Created]),ALLEXCEPT('Table','Table'[ID])) Duration per case = DATEDIFF('Table'[Earliest],'Table'[Oldest],DAY)
AVG = AVERAGE('Table'[Duration per case])
Measure:
Average = AVERAGE('Table'[Duration per case])
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Xue Ding,
You are correct, my error here.
Thank you for help, i managed to do it with your solution.
Regards
Filarap
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 |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |