if statement

[klb0066]

Creating a splicer with title "Has Due Date?" with True or False checkboxes. Coded an if statement that recognizes if field was left bank then it is false and if the field has a date it is true.

hasDue = if(ISBLANK(MX_TICKET[TARGETFINISH]),FALSE(),TRUE())

Would like to add an overdue option. How do I add this to my statement and link current date to due date then identify if it overdue?

[Smauro]

Hey @klb0066

I'm guessing [hasDue] is a column, so, you could either a) create a new column and refresh the model every day or b) have a measure and have a table visual with what you need.

a) 

isDue = IF ( [hasDue] && [TARGETFINISH] <= TODAY() , TRUE(), FALSE() )

Which checks if your [hasDue] is true and the finish target was before or today. One thing you may want to check with your data is whether you have items which are already finished in your table. If so you'll need another clause like NOT([Completed]) or [CompletedDate]=BLANK()

b) 

isDue =
VAR d =
    SELECTEDVALUE ( MX_TICKET[hasDue] )
VAR tf =
    SELECTEDVALUE ( MX_TICKET[TARGETFINISH] )
RETURN
    IF ( d && tf <= TODAY (), TRUE (), FALSE () )

Which does the same as previous, but the table visual should contain a table key (meaning no row is aggregated)

[v-juanli-msft]

Hi @klb0066

Create a calculated column

overdue = IF(ISBLANK([target]),BLANK(),IF([date]>[target],1,0) )

Best Regards

Maggie

[klb0066]

Created this statement for a splicer

hasDue = if(ISBLANK(MX_TICKET[TARGETFINISH]),FALSE(),TRUE())

Identifies blank entries as false and entries that have a date as true. I want to add and overdue checkbox. How do I relate current date to due dates then pull out over due pieces?

[parry2k]

what is the logic to create overdue option?

[klb0066]

It is for work and you are allowed to enter the due date or enter no due date (we are changing this procedure as entering in no due date is pointless now). I want to be able to see what work is overdue that has a due date.

[v-juanli-msft]

Hi @klb0066

What do you mean by "you are allowed to enter the due date or enter no due date"?

If your current date is a column which exsits in the table, you can use the following formula

create calculated column, then add this column to a slicer

hasDue = if(ISBLANK(MX_TICKET[TARGETFINISH]),FALSE(),TRUE())

cretae a measure, then add this measure to the Visual Filter

if over due = IF(MAX([TARGETFINISH])>MAX([current date]),1,0)

If the current date refer to today, you can use TODAY( ) to replace [current date].

Best Regards

Maggie