IF Dates Less Than Or Blank

[HenryJS]

Hi all,

How can I create a column/measure which states the below:

IF [Combined Business Insurance Policy] is blank or less than today

Return "Insurance Missing"

OR

IF [Proffesional Indemnity Insurance Policy] AND [Public Liability Insurance] is blank or less than today

Return "Insurance Missing"

[v-deddai1-msft · Accepted solution]

Hi @HenryJS ，

Would you please try the following calculated column, it will work:

Column =
IF (
    ISBLANK ( 'Table'[Combined Business Insurance Policy] )
        || 'Table'[Combined Business Insurance Policy] < TODAY (),
    "Insurance Missing",
    IF (
         (
            'Table'[Proffesional Indemnity Insurance Policy] < TODAY ()
                || ISBLANK ( 'Table'[Proffesional Indemnity Insurance Policy] )
        )
            && (
                'Table'[Public Liability Insurance] < TODAY ()
                    || ISBLANK ( 'Table'[Public Liability Insurance] )
            ),
        "Insurance Missing",
        BLANK ()
    )
)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[v-deddai1-msft]

Please try my edit formula above again.

[HenryJS]

@v-deddai1-msft it's not right, 

if combined insurance is there and greater today then insurance shouldn't be missing

if professional and public are there and greater today then insurance shouldnt be missing

[v-deddai1-msft]

Hi @HenryJS ,

Would you please try to use the following measure:

Column = SWITCH (
TRUE (),
ISBLANK ( 'Table'[Combined Business Insurance Policy] )
|| 'Table'[Combined Business Insurance Policy] < TODAY (), "Insurance Missing",
(
'Table'[Proffesional Indemnity Insurance Policy] < TODAY ()
|| ISBLANK ( 'Table'[Proffesional Indemnity Insurance Policy] )
)
&& (
'Table'[Public Liability Insurance] < TODAY ()
|| ISBLANK ( 'Table'[Public Liability Insurance] )
), "Insurance Missing",
BLANK ()
)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[HenryJS]

@v-deddai1-msft that doesn't work unforunately as the insurance fields are columns not measures

[amitchandak]

@HenryJS , Try like

Switch( True(),
isblank([Combined Business Insurance Policy]) || [Combined Business Insurance Policy] < today() , "Insurance Missing",
[Proffesional Indemnity Insurance Policy] < today() &&  [Public Liability Insurance] < today() ,"Insurance Missing",
blank())

[HenryJS]

@amitchandak not pulling through correctly

needs to be

IF [Combined Business Insurance Policy] is blank or less than today

Return "Insurance Missing"

OR

IF [Proffesional Indemnity Insurance Policy] AND [Public Liability Insurance] is blank or less than today

Return "Insurance Missing"

OR if