topic Re: Switch or IIF expression for calculated field in Report Server

Switch or IIF expression for calculated field

kirstenvo — Mon, 19 Jul 2021 13:18:51 GMT

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

Re: Switch or IIF expression for calculated field

Greg_Deckler — Mon, 19 Jul 2021 13:26:37 GMT

@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" )

Re: Switch or IIF expression for calculated field

kirstenvo — Mon, 19 Jul 2021 13:36:43 GMT

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

Re: Switch or IIF expression for calculated field

Greg_Deckler — Mon, 19 Jul 2021 13:46:35 GMT

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

Re: Switch or IIF expression for calculated field

kirstenvo — Mon, 19 Jul 2021 13:54:19 GMT

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

Re: Switch or IIF expression for calculated field

Greg_Deckler — Mon, 19 Jul 2021 14:26:06 GMT

@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

Re: Switch or IIF expression for calculated field

kirstenvo — Mon, 19 Jul 2021 14:37:46 GMT

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

Re: Switch or IIF expression for calculated field

Greg_Deckler — Mon, 19 Jul 2021 17:13:15 GMT

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

Re: Switch or IIF expression for calculated field

TomMartens — Mon, 19 Jul 2021 18:41:15 GMT

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

Re: Switch or IIF expression for calculated field

kirstenvo — Tue, 20 Jul 2021 06:34:30 GMT

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

Re: Switch or IIF expression for calculated field

TomMartens — Tue, 20 Jul 2021 18:38:35 GMT

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

Re: Switch or IIF expression for calculated field

kirstenvo — Thu, 22 Jul 2021 06:37:47 GMT

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

Re: Switch or IIF expression for calculated field

TomMartens — Thu, 22 Jul 2021 07:39:29 GMT

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

Re: Switch or IIF expression for calculated field

kirstenvo — Tue, 04 Oct 2022 12:02:54 GMT

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

Re: Switch or IIF expression for calculated field

kirstenvo — Tue, 04 Oct 2022 12:03:32 GMT