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 ,

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

  • 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

  • kirstenvo's avatar
    kirstenvo
    Frequent Visitor
    5 years ago

    TomMartens The expression is working but not showing exactly the right data, in the example below, the two records, the planned completion date is  after the actual completion date so the Overdue? column should show "On Time". What do I have to change to the expression to have this shown correctly?

     

     

  • 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