Forum Discussion

gauravnarchal's avatar
gauravnarchal
Icon for Post Prodigy rankPost Prodigy
5 years ago
Solved

If date is Less than greater than today Measure

Hello

 

I want to create a measure to get the result as–

 

If the contract date is 1-Jan-00  then return “Contract Expired – High”

If the contract end date is within 60days as of today then return “Contract Expiring Soon – Level 2”

If the contract end date is within 30days as of today then return “Contract Expiring Soon – Level 1”

If the contract end date is less than or equal to “as of today” then return “Contract Expired”

Else

"Contract Valid"

 

I have attached the PBIX for your reference - Download Here

 

Thanks

Gaurav

 

 

Contract Start DateContract End Date
15-Feb-1814-Feb-21
1-Apr-171-Jan-00
28-Jan-1427-Jan-21
1-Jan-1831-Dec-21
19-Nov-2021-Nov-21
4-Aug-1930-Jul-21
1-Jul-1630-Jun-21
21-May-2021-May-21
1-Sep-171-Sep-20
15-Jan-2015-Jan-21
20-Aug-1320-Aug-11
1-Apr-191-Apr-20
1-Apr-191-Apr-20
31-Aug-2031-Oct-23
Need Help
Show and Tell
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    Please try this measure expression in a table visual with your end date column.

     

    Contract Status =
    VAR vDaysFromExpiry =
        DATEDIFF (
            TODAY (),
            MIN ( Sheet1[Contract End Date] ),
            DAY
        )
    VAR vSwitch =
        SWITCH (
            TRUE (),
            vDaysFromExpiry < 0, "Contract Expired",
            vDaysFromExpiry <= 30, "Contract Expiring Soon - Level 1",
            vDaysFromExpiry <= 60, "Contract Expiring Soon - Level 2",
            "Other"
        )
    RETURN
        IF (
            MIN ( Sheet1[Contract End Date] )
                = DATE ( 2000, 1, 1 ),
            "Contract Expired - High",
            vSwitch
        )

     

    Regards,

    Pat

  • v-janeyg-msft's avatar
    v-janeyg-msft
    5 years ago

    Hi, gauravnarchal 

     

    It’s my pleasure to answer for you.

    You can modify the previous formula like this:

    If it doesn’t solve your problem, please feel free to ask me.

     

    Best Regards

    Janey Guo

     

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

4 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    Please try this measure expression in a table visual with your end date column.

     

    Contract Status =
    VAR vDaysFromExpiry =
        DATEDIFF (
            TODAY (),
            MIN ( Sheet1[Contract End Date] ),
            DAY
        )
    VAR vSwitch =
        SWITCH (
            TRUE (),
            vDaysFromExpiry < 0, "Contract Expired",
            vDaysFromExpiry <= 30, "Contract Expiring Soon - Level 1",
            vDaysFromExpiry <= 60, "Contract Expiring Soon - Level 2",
            "Other"
        )
    RETURN
        IF (
            MIN ( Sheet1[Contract End Date] )
                = DATE ( 2000, 1, 1 ),
            "Contract Expired - High",
            vSwitch
        )

     

    Regards,

    Pat

    • gauravnarchal's avatar
      gauravnarchal
      Icon for Post Prodigy rankPost Prodigy
      5 years ago

      mahoneypat  - Thank you for the advice.. I had created the measure and it is showing the desired result, when i try to filter the results status from "Filter on this visual"  i am not able to do it. Can you please help!

       

       

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    That seems to be working for me (see pic).  If you resend your pbix, I can take a look.

     

     

    Regards,

    Pat

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, gauravnarchal 

     

    It’s my pleasure to answer for you.

    You can modify the previous formula like this:

    If it doesn’t solve your problem, please feel free to ask me.

     

    Best Regards

    Janey Guo

     

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