Forum Discussion

kirstenvo's avatar
kirstenvo
Frequent Visitor
5 years ago
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 ...
expression
  • TomMartens's avatar
    TomMartens
    5 years ago

    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,
    Tom

  • TomMartens's avatar
    TomMartens
    5 years ago

    Hey 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

TomMartens's avatar
TomMartens
Super User
5 years ago

hey kirstenvo ,

 

my data looks like this:

the expression:

=IIF(IsNothing(Fields!PlannedDueDate.Value)
, IIF( Fields!ActualCompletion.Value > Today(), "Overdue", "unexpected")
, IIF( Fields!PlannedDueDate.Value < Fields!ActualCompletion.Value , "Overdue" , "OnTime")
)

alltogether

 

hopefully, this provides what you are looking for.

 

Regards,

Tom

kirstenvo's avatar
kirstenvo
Frequent Visitor
5 years ago

Hi TomMartens , Thank you for your help! Planned completion date is never empty, only actual completion date can be empty. I have now figured out the expression below and that one works almost perfect for me :). I now have only one thing to solve and that is that i have an action with the planned completion date and actual completion date on the same day but probably on a different time causing it to say "Overdue". I want the expression to don't look at the time, only the date, can you help me with that?

 

 

 

My expression now:

= IIF( IsNothing(Fields!ActualCompletionDate.Value) And Fields!PlannedCompletionDate.Value < TODAY(), "Overdue" ,IIF(Fields!ActualCompletionDate.Value > Fields!PlannedCompletionDate.Value , "Overdue" , "On Time") )

 

Kirsten

  • TomMartens's avatar
    TomMartens
    Super User
    5 years ago

    Hey 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