Duration Calculation between different dates

[mohammadyousaf]

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. 

[lbendlin · Accepted solution]

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!