cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kirstenvo
Frequent Visitor

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

2 ACCEPTED SOLUTIONS

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:
image.png



Hopefully, this provides what you are looking for.

Regards,
Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

12 REPLIES 12
kirstenvo
Frequent Visitor

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

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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

 

kirstenvo_0-1626702831433.png

 

@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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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:
image.png



Hopefully, this provides what you are looking for.

Regards,
Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

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

 

kirstenvo_1-1626762860860.png

 

hey @kirstenvo ,

 

my data looks like this:

image.png

the expression:

=IIF(IsNothing(Fields!PlannedDueDate.Value)
, IIF( Fields!ActualCompletion.Value > Today(), "Overdue", "unexpected")
, IIF( Fields!PlannedDueDate.Value < Fields!ActualCompletion.Value , "Overdue" , "OnTime")
)

alltogether

image.png

 

hopefully, this provides what you are looking for.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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?

 

kirstenvo_0-1626935859034.png

 

 

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

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

Greg_Deckler
Super User IV
Super User IV

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

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.