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

kirstenvo's avatar
kirstenvo
Frequent Visitor
5 years ago

Greg_Deckler Thank you for your help so far. I have read the article you send. For me it seems possible to show what i want to show, is it? And if so, are you able to help me with it or do I need to search help somewhere else? 

  • 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

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

       

       

      • 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