Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

14 REPLIES 14
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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

@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
Greg_Deckler
Super User
Super User

@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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Screenshot 2022-10-04 140017.png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.