Handle multiple empty rows in summariz

New table =

SUMMARIZE(

'Upgrade','Upgrade'[Client],

'Upgrade'[start date],

'Upgrade'[completion date],

)

I have a custom column "Days" to calculate the number of days between the start date and completion date.

The problem is some rows in the start date and completion date have no dates.

Question 1: I want is to have a 0 in the calculated column "Days" if there are no dates in both columns (start date, completion date).

Question 2: Is it possible to have a condition in summarize to replace the empty values with 0 such as if(isblank(start date) show zero.

I tried using the IF statement but isn't working.

Sample Table

Solution 1:

This will eliminate all start date=BLANK and completion date=BLANK

tbl =
SUMMARIZE (
    FILTER (
        'Upgrade',
        ISBLANK ( 'Upgrade'[start date] ) = FALSE ()
            && ISBLANK ( 'Upgrade'[completion date] ) = FALSE ()
    ),
    'Upgrade'[Client],
    'Upgrade'[start date],
    'Upgrade'[completion date]
)

Solution 2:

Out of curiosity, why are you trying to return a table with the SUMMARIZE() function? For starters, it's recommended to use SUMMARIZECOLUMNS() instead for performance reasons. But couldn't you accomplish the Total Days column condition by making a calculate column instead?

Total Days = 
VAR BlankDates = ISBLANK( Upgrade[Start Date] ) && ISBLANK( Upgrade[Completed Date] )
VAR TotalDays = DATEDIFF( Upgrade[Start Date], Upgrade[Completed Date], DAY )
RETURN IF( BlankDates, 0, TotalDays )

Output:

However, if you absolutely need the output to be a new table. I would do something like this here.

New Table = 
ADDCOLUMNS(
    SUMMARIZECOLUMNS(
        Upgrade[Client],
        Upgrade[Start Date],
        Upgrade[Completed Date],
        "Extra", 1
    ),
    "Total Days",
        IF(
            ISBLANK( [Start Date] ) && ISBLANK( [Completed Date] ),
            0,
            DATEDIFF( [Start Date], [Completed Date], DAY )
        )
)

Output: