Forum Discussion
Solved
Switch or IIF expression for calculated field
Hi there, I have two fields that i want to use to calculate a third field: "Planned due date" and "Actual Completion Date". I want to achieve the following: If Planned Due Date is empty and ...
Hey kirstenvo ,
I assume that you use a textbox, then this expression maybe provides what you are looking for:= IIF( IsNothing(Fields!PlannedCompletion.Value) And Fields!ActualCompletion.Value < TODAY(), "over due" ,IIF(Not(IsNothing(Fields!PlannedCompletion.Value)) And Fields!PlannedCompletion.Value <Fields!ActualCompletion.Value , "over due" , "on time") )Please be aware that IsNothing is the name of the ReportingBuilder (SQL Server Reporting Services) function used for inspection instead of IsBlank (a DAX function).
My data:
Hopefully, this provides what you are looking for.
Regards,
TomHey kirstenvo ,
you can get rid of the Time part using the function FormatDateTime( ... , DateFormat.ShortDate) like so
FormatDateTime(Fields!ActualCompletionDate.Value , DateFormat.ShortDate)Of course you have to wrap both column references in FormatDateTime 😉
Hopefully, this provides what you are looking for.
Regards,
Tom
Hi Greg,
I am working with the formula for a while but I have noticed another problem, I am using the following DAX formula:
Overdue? = SWITCH (TRUE(),ISBLANK([ReviewedDate]) && [DueDate] < TODAY(), "Overdue", [DueDate] < [ReviewedDate], "Overdue", [DueDate] = [ReviewedDate], "On Time", "On Time")
However, lines with the same DueDate and ReviewedDate still show "Overdue".
As you can see in the screenshot, there is a record (DocumentID 4477) with DueDate and ReviewedDate on same time. The outcome should be "On Time" but it is "Overdue". Can you help me, what am I missing?
Kind regards,
Kirsten