Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper I
Helper I

Calculating through empty cells (nested ISBLANK)



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 1Stage 2Stage 3Stage 4Stage 5CompletedLost
Project 101/01/202320/01/202301/02/202310/02/202320/02/202301/03/2023 
Project 201/01/2023 15/02/202320/02/202301/03/202310/03/2023 
Project 301/01/2023  20/01/2023   
Project 4  10/01/202320/01/202320/02/202302/03/2023 
Project 501/01/2023     10/01/2023
Project 6   01/02/202320/02/2023 10/03/2023
Project 710/02/2023      



How the result should look like:


Days in Stage 1Days in Stage 2Days in Stage 3Days in Stage 4Days in Stage 5


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.


Helper I
Helper I

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 🙂

Helper I
Helper I

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 = 
    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])))))))



Helper I
Helper I

@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

Super User
Super User

@Nedix , 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 )

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors