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 Actual Completion Date is < Today, then the third column should show "Overdue"

If Planned Due Date < Actual Completion Date then the column should also show "Overdue". otherwise it should show "On Time" .

 

Can anyone help me with this?

 

Kirsten

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

14 Replies

  • 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
    5 years ago

    Greg_Deckler thank you for your quick reply. The expression is not working for me (yet)

     

    My expression: 

    =SWITCH(TRUE(), ISBLANK(Fields!PlannedDueDate.Value)&&Fields!ActualCompletionDate.Value<TODAY(),"Overdue",Fields!PlannedDueDate.Value<Fields!ActualCompletionDate.Value,"Overdue","On Time")

     

    I select the Fields (DataSet1) in the catagory, there is where I get this format. I did try to replace "Fields!PlannedDueDate.Value" into [Planned Due Date] etc. but that didn't work either. 

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      5 years ago

      kirstenvo First, need to understand if this is a measure or column calculation. If Fields is your table name, then should be 'Fields'[PlannedDueDate] if PlannedDueDate is your column name. Same for the other references. If this is a measure, then you will need aggregators around your columns like: MAX('Fields'[PlannedDueDate]).

       

      If this is some kind of calculation that is not DAX but being done some other way in your paginated report authoring tool, I don't know the syntax for that.

      • kirstenvo's avatar
        kirstenvo
        Frequent Visitor
        5 years ago

        Greg_Deckler I am quite new to Power BI Report Builder, i have added a Data Set (DataSet1) which contains the fields that i want to use, then I added a table and dragged all the required fields into it one by one.  Then I clicked on "Insert Column" where I wanted to show the calculated value.  If I right-click on that field and click on expression, I get the pop-up that you see in the screenshot.