Forum Discussion
Duration Calculation between different dates
Hi.. I tried my best but I am stuck and requesting your help for the following from given table.
1. Planned Duration which I can calcualte using= DATEDIFF(MIN('Project Details Sheet'[Planned Start Date]), MAX('Project Details Sheet'[Planned Completion date]),DAY)+0
2. Actual Duration when Task is not in selection, means Total of Actual Duration as of today, but if All tasks has dates than it should not take Today() in calculations and instead take the last date (of K).
3. Actual Duration when Task is focus
4. Actual Duration should show "In Progress" if task has blank completion
5. Duration Remaining in all the above cases by taking the planned completion date in consideration
6. % of Duration , +- in case of days are more than the planned.
- These are Cards
| Tasks | Actual Start Date | Actual Completion Date | Planned Start Date | Planned Completion Date |
| A | 25/08/20 | 05/09/20 | 09/01/20 | 09/07/20 |
| B | 15/09/20 | 15/02/21 | 29/09/20 | 11/07/21 |
| C | 07/12/20 | 15/03/21 | 14/11/20 | 22/01/21 |
| D | 20/12/20 | 18/04/21 | 14/11/20 | 22/01/21 |
| E | 01/02/21 | 11/03/21 | 01/02/21 | 11/03/21 |
| F | 09/09/20 | 09/02/21 | 19/09/20 | 28/09/20 |
| G | 28/11/20 | 03/01/21 | 18/10/20 | 25/11/20 |
| H | 07/04/21 | 16/05/21 | 07/04/21 | 10/05/21 |
| I | 11/04/21 | 11/04/21 | 05/09/21 | |
| J | 17/03/21 | 29/03/21 | 21/04/21 | 02/05/21 |
| K | 07/06/21 | 18/05/21 | 10/09/21 |
Thanks a lot.
You may want to look into using the COALESCE function.
For your "actual completion date" being blank scenario it would say
Actual Completion = COALESCE([Actual Completion Date],TODAY())
meaning that if the date is missing it would be supplemented by the current date.
word of warning! TODAY() depends on either user interaction (if used in a measure) or frequent/daily dataset refreshes (if used in a calculated column). It is not automatic!
1 Reply
You may want to look into using the COALESCE function.
For your "actual completion date" being blank scenario it would say
Actual Completion = COALESCE([Actual Completion Date],TODAY())
meaning that if the date is missing it would be supplemented by the current date.
word of warning! TODAY() depends on either user interaction (if used in a measure) or frequent/daily dataset refreshes (if used in a calculated column). It is not automatic!