Forum Discussion

GON76's avatar
GON76
Helper III
1 year ago
Solved

Using two conditions to return a specific value

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?
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    1 year ago

    Hi,

    Write this calculated column formula

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

    Hope this helps.

9 Replies

  • andrewsommer's avatar
    andrewsommer
    Super User
    1 year ago

    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's avatar
      GON76
      Helper III
      1 year ago

      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's avatar
        MohamedFowzan1
        Super User
        1 year ago

        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

  • Bmejia's avatar
    Bmejia
    Super User
    1 year ago

    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's avatar
      GON76
      Helper III
      1 year ago

      I just tried that and nothing seems to have changed

       

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    1 year ago

    Hi,

    Write this calculated column formula

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

    Hope this helps.

  • v-priyankata's avatar
    v-priyankata
    Community Support
    1 year ago

    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's avatar
      v-priyankata
      Community Support
      1 year ago

      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's avatar
        v-priyankata
        Community Support
        1 year ago

        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.