Hello everyone,
I hope you are all safe an sound/
I need some assistance with a time calculation.
So i have three columns: a tracking number for each task and a start and last update dates columns as you can see on my extract.
I hace tried a couple measure using dateiff but i always end up either with the wrong time or an error on my calculated columns and measures.
| tracking # | date of creation | date of update |
| 10001 | 9/16/2019 00:00 | 9/26/2019 00:00 |
| 10002 | 9/16/2019 00:00 | 9/20/2019 00:00 |
| 10003 | 9/16/2019 00:00 | 11/1/2019 00:00 |
| 10004 | 9/16/2019 00:00 | 9/30/2019 00:00 |
| 10005 | 9/16/2019 00:00 | 10/7/2019 00:00 |
| 10006 | 9/16/2019 00:00 | 10/10/2019 00:00 |
| 10007 | 9/18/2019 00:00 | 11/1/2019 00:00 |
| 10008 | 9/18/2019 00:00 | 10/10/2019 00:00 |
| 10009 | 9/19/2019 00:00 | 11/11/2019 00:00 |
| 10010 | 9/19/2019 00:00 | 9/27/2019 00:00 |
| 10011 | 9/19/2019 00:00 | 9/27/2019 00:00 |
| 10012 | 9/19/2019 00:00 | 10/7/2019 00:00 |
| 10013 | 9/19/2019 00:00 | 9/19/2019 00:00 |
| 10014 | 9/20/2019 00:00 | 9/23/2019 00:00 |
| 10015 | 9/20/2019 00:00 | 9/20/2019 00:00 |
| 10016 | 9/20/2019 00:00 | 11/22/2019 00:00 |
| 10017 | 9/20/2019 00:00 | 11/1/2019 00:00 |
| 10018 | 9/23/2019 00:00 | 11/6/2019 00:00 |
| 10019 | 9/23/2019 00:00 | 10/7/2019 00:00 |
| 10020 | 9/23/2019 00:00 | 9/23/2019 00:00 |
| 10021 | 9/23/2019 00:00 | 10/10/2019 00:00 |
| 10022 | 9/24/2019 00:00 | 10/23/2019 00:00 |
| 10023 | 9/24/2019 00:00 | 12/9/2019 00:00 |
| 10024 | 9/24/2019 00:00 | 10/17/2019 00:00 |
| 10025 | 9/24/2019 00:00 | 9/24/2019 00:00 |
| 10026 | 9/25/2019 00:00 | 9/25/2019 00:00 |
| 10027 | 9/26/2019 00:00 | 10/22/2019 00:00 |
| 10029 | 9/26/2019 00:00 | 9/26/2019 00:00 |
| 10030 | 9/26/2019 00:00 | 11/19/2019 00:00 |
| 10031 | 9/26/2019 00:00 | 9/26/2019 00:00 |
| 10034 | 9/26/2019 00:00 | 10/11/2019 00:00 |
| 10035 | 9/26/2019 00:00 | 9/30/2019 00:00 |
| 10037 | 9/27/2019 00:00 | 10/15/2019 00:00 |
| 10038 | 9/27/2019 00:00 | 10/11/2019 00:00 |
| 10039 | 9/27/2019 00:00 | 10/14/2019 00:00 |
| 10040 | 9/30/2019 00:00 | 9/30/2019 00:00 |
| 10041 | 9/30/2019 00:00 | 11/1/2019 00:00 |
| 10043 | 9/30/2019 00:00 | 10/7/2019 00:00 |
| 10044 | 9/30/2019 00:00 | 10/7/2019 00:00 |
| 10045 | 9/30/2019 00:00 | 10/7/2019 00:00 |
| 10046 | 9/30/2019 00:00 | 10/7/2019 00:00 |
| 10047 | 9/30/2019 00:00 | 12/5/2019 00:00 |
| 10048 | 9/30/2019 00:00 | 10/1/2019 00:00 |
| 10049 | 10/1/2019 00:00 | 10/1/2019 00:00 |
| 10050 | 10/1/2019 00:00 | 10/1/2019 00:00 |
| 10051 | 10/1/2019 00:00 | 10/1/2019 00:00 |
| 10052 | 10/1/2019 00:00 | 11/14/2019 00:00 |
| 10053 | 10/2/2019 00:00 | 10/7/2019 00:00 |
| 10054 | 10/2/2019 00:00 | 10/14/2019 00:00 |
I would like to show on y report two different calculations using the following logic:
- A column or measure to display the time taken to finish a task (days between start and last update date)
- The average time it takes to complete a task in general (i just need an average estimation although i know there will be outliers to distort the average) and i think the previous column or measure would help in this calculation.
if someone can really help me with this i would really appreciate it.
Thanks in advance.
