Calculating through empty cells (nested ISBLANK)

[Anonymous]

Hello,

I have a problem calculating multiple ISBLANK conditions. The problem statement is:

- I have multiple projects which go through different stages

- sometimes, the projects skip stages or come directly in later stages

- sometimes projects get lost

I want to calculate how long do the projects stay in each stage. If all stages have a date the calculation is easy, I just need to do Stage 2 - Stage 1 to get the Days in Stage 1 duration. But, for example, if the project skipped a stage or more, I need the calculation to follow through, so it should be the next available date minus the initial date. For example, Project 2 - Days in Stage 1 should be = IF Stage 2 is Blank then Days in Stage 1 = Stage 3 - Stage1. For Project 3, Days in Stage 1 = Stage 4 - Stage 1 (sonce Stages 2 and 3 are Blank).

Also, if if a project is in any stage and it's not completed or Lost then the Days in Stage "X" should be Today - Stage "X" (e.g. Project 7 - Days in Stage 1 = IF all the following cells are blanks then calculate Today - Stage 1, and similar for Project 3 - if all the cells after Stage 4 are blanks then Days in Stage 4 = Today - Stage 4.

If for any reason the project is Lost then the calculation should take the last NOT blank date and subtract it from the Lost date. E.g. Project 5 - Days in Stage 1 = Lost - Stage 1

Statement table

	Stage 1	Stage 2	Stage 3	Stage 4	Stage 5	Completed	Lost
Project 1	01/01/2023	20/01/2023	01/02/2023	10/02/2023	20/02/2023	01/03/2023
Project 2	01/01/2023		15/02/2023	20/02/2023	01/03/2023	10/03/2023
Project 3	01/01/2023			20/01/2023
Project 4			10/01/2023	20/01/2023	20/02/2023	02/03/2023
Project 5	01/01/2023						10/01/2023
Project 6				01/02/2023	20/02/2023		10/03/2023
Project 7	10/02/2023

How the result should look like:

Days in Stage 1	Days in Stage 2	Days in Stage 3	Days in Stage 4	Days in Stage 5
19	12	9	10	9
45	0	5	9	9
19	0	0	60	0
0	0	10	31	10
9	0	0	0	0
0	0	0	19	18
39	0	0	0	0

PS: I tried doing it with the Switch function and do ISBLANKs line by line but it works only if there is one gap between dates - e.g. Project 2 and at some point it just gives up

Any help is very much appreciated.

@me 

[Anonymous]

For whomever is interested, I realized there are two more cases which weren't considered:

1. if a cell is blank and you fill it with 0 then if you do AVG on the column it will then take the row and add it to the denominator of the AVG. You may want to avoid this becuase you don't want to count projects which did not (naturally) made it to or through that Stage. E.g. if a project is still in Stage 1 you want to ignore it if you do an AVG on Stage 2 but, if the project skipped Stage 2 and jumped to Stage 3 then you want to take the 0 value of Stage 2
2. never underestimate the user's capability to screw data. More often than not, I found the situation where the project skipped a Stage but later on reverted to the skipped Stage. YOu will end up with a negative number of the days that the project spent in the skipped Stage. I mitigated this by creating a new column (🙄) and doing something simple like IF [Column] <0, 0, [Column]

Hope it makes sense. 

The end solution is ugly af but it works 🙂

[Anonymous]

I did it in a very primitive way. It's not pretty at all but it works as intended; rinse and repeat for the other columns.  If anyone finds an elegant way to do it I would be happy to test it out. 

DS1a is the newly created column and Summary is the name of the table

DS1a = 
if(
    ISBLANK(Summary[Stage 1]), 0,
    IF(NOT(ISBLANK(Summary[Error/Duplicate]))&&ISBLANK(Summary[Stage 2])&&ISBLANK(Summary[Stage 3])&&ISBLANK(Summary[Stage 4])&&ISBLANK(Summary[Stage 5])&&ISBLANK(Summary[Completed]),Summary[Error/Duplicate]-Summary[Stage 1],
        IF(NOT(ISBLANK(Summary[Lost]))&&ISBLANK(Summary[Stage 2])&&ISBLANK(Summary[Stage 3])&&ISBLANK(Summary[Stage 4])&&ISBLANK(Summary[Stage 5])&&ISBLANK(Summary[Completed]),Summary[Lost]-Summary[Stage 1],
            if(NOT(ISBLANK(Summary[Completed]))&&ISBLANK(Summary[Stage 2])&&ISBLANK(Summary[Stage 3])&&ISBLANK(Summary[Stage 4])&&ISBLANK(Summary[Stage 5]),Summary[Completed]-Summary[Stage 1],
                if(NOT(ISBLANK(Summary[Stage 1]))&&NOT(ISBLANK(Summary[Stage 2])),Summary[Stage 2]-Summary[Stage 1],
                    IF(NOT(ISBLANK(Summary[Stage 1]))&&ISBLANK(Summary[Stage 2])&&NOT(ISBLANK(Summary[Stage 3])),Summary[Stage 3]-Summary[Stage 1],
                        if(NOT(ISBLANK(Summary[Stage 1]))&&ISBLANK(Summary[Stage 2])&&ISBLANK(Summary[Stage 3])&&NOT(ISBLANK(Summary[Stage 4])),Summary[Stage 4]-Summary[Stage 1],
    TODAY()-Summary[Stage 1])))))))

[Anonymous]

@amitchandak, I appreciate the answer but it is incomplete.
1. If the project jumped Stage 2 (see Project 2) then the formula returns a blank

2. If the project was Lost from Stage 1 (see Project 5) then the formula returns 0 instead of Lost - Stage 1

3. If the project is still in Stage 1 (see Project 7) then the formula returns an error instead of TODAY()-Stage 1

[amitchandak]

@Anonymous , if those are columns you have create new column like

Stage 1 days = if(not(isblank([Stage1])) && not(isblank([Stage3])) , datediff([Stage1], [Stage2], day) , 0 )