Switch or IIF expression for calculated field

[kirstenvo]

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

[TomMartens · Accepted solution]

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]

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

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

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

[Greg_Deckler]

@kirstenvo Yeah, what I provided was to create a new column in your dataset using DAX. DAX does not work in Report Builder. Completely different syntax. I recommend this reference as a start: Expressions (Report Builder) - SQL Server Reporting Services (SSRS) | Microsoft Docs

[kirstenvo]

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

[Greg_Deckler]

@kirstenvo Sorry, I can't really help with Report Builder equation stuff. Not my area of speciality. Maybe @TomMartens or @mahoneypat  

[TomMartens · Accepted solution]

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]

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

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]

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 · Accepted solution]

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]

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

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]