Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Calculating through empty cells (nested ISBLANK)

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 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
19129109
450599
1900600
00103110
90000
0001918
390000

 

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 

4 REPLIES 4
Anonymous
Not applicable

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
Not applicable

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
Not applicable

@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
Super User
Super User

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors