Using two conditions to return a specific value

[GON76]

I am trying to use two conditions to return a value.  I'm using the following formula:

Compliance Status =

IF(OR

    ('V_ENROLLMENTS'[EXITDATE].[Date] < EDATE(TODAY(), -3), ISBLANK([EXITDATE])),

    "Not Compliant",

    "Compliant"

)

I want to show "Not Compliant" for all EXIT dates that are over 3 years old from today and for EXITDATES that are blank.  If the EXITDATE is less than 3 years, and the EXITDATE field is populated, I want it to show "Compliant".  Is my formula correct?

[v-priyankata]

Hi @GON76 

Thank you for reaching out to the Microsoft Fabric Forum Community.

@andrewsommer @MohamedFowzan1 @Ashish_Mathur @Bmejia Thanks for your inputs.

I hope the information shared by other users was helpful. Have you had a chance to try it? Are you experiencing any other issues? If you have more questions, feel free to ask the community.

[v-priyankata]

Hi @GON76 

I wanted to check if you had the opportunity to review the information provided by users. Please feel free to contact us if you have any further questions.

[v-priyankata]

Hi @GON76 

Hope everything’s going smoothly on your end. I wanted to check if the issue got sorted. if you have any other issues please reach community.

[Ashish_Mathur · Accepted solution]

Hi,

Write this calculated column formula

=if(or('V_Enrollments'[Exitdate]=blank(),'V_Enrollments'[Exitdate]<=edate(today(),-36)),"Not compliant","Compliant")

Hope this helps.

[Bmejia]

GON76, 

I believe you just need to change the -3 to -36, because Edate is base on months so 36 months would be 3 years.

[GON76]

I just tried that and nothing seems to have changed

[andrewsommer]

Try this:

Compliance Status =
IF (
    OR (
        'V_ENROLLMENTS'[EXITDATE] < DATE(YEAR(TODAY()) - 3, MONTH(TODAY()), DAY(TODAY())),
        ISBLANK('V_ENROLLMENTS'[EXITDATE])
    ),
    "Not Compliant",
    "Compliant"
)

Please mark this post as a solution if it helps you. Appreciate Kudos.

[GON76]

Do I need to do something when I make an update to a table in order to get it to reflect on my report view?  I'm still seeing Compliant when the EXITDATE is missing.

[MohamedFowzan1]

Could you debug using a calculated column and confirm if the dax is actually reading these dates as blank using the below:
CheckBlank = IF(ISBLANK('V_ENROLLMENTS'[EXITDATE]), "Blank", "Not Blank")

Try this as well to confirm:
Compliance Status =
IF (
ISBLANK('V_ENROLLMENTS'[EXITDATE]),
"EXIT Date Missing",
IF(
'V_ENROLLMENTS'[EXITDATE] < DATE(YEAR(TODAY()) - 3, MONTH(TODAY()), DAY(TODAY())),
"Not Compliant",
"Compliant"
)
)

and proceed with

Compliance Status =
IF (
ISBLANK('V_ENROLLMENTS'[EXITDATE]), 
"Not Compliant",
IF(
'V_ENROLLMENTS'[EXITDATE] < DATE(YEAR(TODAY()) - 3, MONTH(TODAY()), DAY(TODAY())),
"Not Compliant",
"Compliant"
)
)

Incase you were able to pinpoint the issue for the blank dates and it has any other value use || and handle it along with ISBLANK
Let us know if you were able to debug the issue by any chance