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

Greg_Deckler's avatar
Greg_Deckler
Community Champion
5 years ago

kirstenvo Here is some psuedo code:

Column = 
  SWITCH(TRUE(),
    ISBLANK([Planned Due Date]) && [Actual Completion Date] < TODAY(),"Overdue",
    [Planned Due Date] < [Actual Completion Date],"Overdue",
    "On Time"
  )
kirstenvo's avatar
kirstenvo
Frequent Visitor
3 years ago

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
  • kirstenvo's avatar
    kirstenvo
    Frequent Visitor
    3 years ago