Forum Discussion
Creating unique values table with min / max timestamps
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
Hi Anonymous ,
1. You can create measures like so:
Earliest = CALCULATE(MIN('Table 1'[Closed]),ALLEXCEPT('Table 1','Table 1'[ID]))Oldest = CALCULATE(MAX('Table 1'[Created]),ALLEXCEPT('Table 1','Table 1'[ID]))Duration per case = DATEDIFF([Earliest],[Oldest],DAY)
2. Copy “Visual” and remove “Closed” and “Created”, ”Duration” columns, then you will get “Visual 2”.
3. Or, you can create a new table.
Table 3 = ADDCOLUMNS ( VALUES ( 'Table 1'[ID] ), "Earliest", [Earliest], "Oldest", [Oldest], "Duration per case", [Duration per case] )Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi Anonymous ,
1. You can create measures like so:
Earliest = CALCULATE(MIN('Table 1'[Closed]),ALLEXCEPT('Table 1','Table 1'[ID]))Oldest = CALCULATE(MAX('Table 1'[Created]),ALLEXCEPT('Table 1','Table 1'[ID]))Duration per case = DATEDIFF([Earliest],[Oldest],DAY)
2. Copy “Visual” and remove “Closed” and “Created”, ”Duration” columns, then you will get “Visual 2”.
3. Or, you can create a new table.
Table 3 = ADDCOLUMNS ( VALUES ( 'Table 1'[ID] ), "Earliest", [Earliest], "Oldest", [Oldest], "Duration per case", [Duration per case] )Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
Is this problem sloved?If it is sloved, could you kindly accept it as a solution to close this case?If not, please let me know.Best RegardsIcey