Forum Discussion
Remove null in a Calculated Date column
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.
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.
2 Replies
- tamerj1Community Champion
Hi teflonreis
Please tryCalculatedColumn = 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-xiaotangCommunity Support
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.