Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Currently getting an error when doing a DATEDIFF operation on a row that may have a missing start or end date. I've confirmed that the fields used are of the datetime2 data type so I've placed the necessary ISNOTHING() and ISERROR() checks, however I'm still getting the error. ISNOTHING gets triggered when there is a null value if I put it on a separate field but it doesn't seem to work when I have it in the code block below:
=IIF(ISNOTHING(CDATE(Fields!StartDate.Value)) OR ISNOTHING(CDATE(Fields!EndDate.Value)) OR CSTR(Fields!StartDate.Value) = "" OR CSTR(Fields!EndDate.Value) = "" OR ISERROR((DateDiff(DateInterval.day,Fields!StartDate.Value,Fields!EndDate.Value) + 1) - (DateDiff(DateInterval.WeekOfYear,Fields!StartDate.Value,Fields!EndDate.Value) * 2)), "",
(DateDiff(DateInterval.day,Fields!StartDate.Value,Fields!EndDate.Value)+1)
- (DateDiff(DateInterval.WeekOfYear,Fields!StartDate.Value,Fields!EndDate.Value)*2)
- IIF(Weekday(Fields!StartDate.Value,1) = 1,1,0)
- IIF(Weekday(Fields!StartDate.Value,1) = 7,1,0)
- IIF(Weekday(Fields!EndDate.Value,1) = 1,1,0)
- IIF(Weekday(Fields!EndDate.Value,1) = 7,1,0)
)
Any tips on how I can properly catch or bypass the error?
Hi @olimilo
Thanks for reaching out to us.
If you put ISERROR part on a separate field will it work?
I also find some related post, please check if they help:
https://stackoverflow.com/questions/17511151/isnothing-not-working-on-empty-value-in-report-builder
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.