Remove null in a Calculated Date column

[teflonreis]

Hello everyone

I have a newly set up list or table with little data so far.

Inside it, there is a calculated date column that sadly displays 0 although no dates were given.

Looks like this in Query mode. 35 days is right. Every else column wrong since there are no dates given. 
This Null distorts my KPI (Average number of days distorts close to 0 instead of 35).

CalculatedColumn =
( DATEDIFF (Table[OrderDate], Table[DeliveryDate], DAY ) )
- INT (
DATEDIFF ( Table[OrderDate], Table[DeliveryDate], DAY ) / 7
) * 2
- IF (
WEEKDAY ( Table[OrderDate] ) < WEEKDAY ( Table[OrderDate] ),
2,
IF (
OR (
WEEKDAY ( Table[DeliveryDate] ) = 7,
WEEKDAY ( Table[OrderDate] ) = 1
),
1,
0
)
)

In a row that does not have dates to calculate the difference of, it just displays 0.

How can I adapt it such that

only if both dates are given, it calculates the difference. Else does not calculate anything. And not giving me null by calculating the difference of nulls.

Thanks for any hints.

[tamerj1]

Hi @teflonreis 
Please try

CalculatedColumn =
DATEDIFF ( Table[OrderDate], Table[DeliveryDate], DAY )
    - INT ( DATEDIFF ( Table[OrderDate], Table[DeliveryDate], DAY ) / 7 ) * 2
    - IF (
        WEEKDAY ( Table[OrderDate] ) < WEEKDAY ( Table[OrderDate] ),
        2,
        IF (
            OR ( WEEKDAY ( Table[DeliveryDate] ) = 7, WEEKDAY ( Table[OrderDate] ) = 1 ),
            1
        )
    )

By the way, what do you mean by this condition?

[v-xiaotang · Accepted solution]

Hi @teflonreis 

>> only if both dates are given, it calculates the difference. Else does not calculate anything.

Please add if judgment before your calculation statement, 

IF('Table'[OrderDate]<>BLANK() && 'Table'[DeliveryDate]<>BLANK(), your expression)

e.g.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.