If date is Less than greater than today Measure

[gauravnarchal]

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 Date	Contract End Date
15-Feb-18	14-Feb-21
1-Apr-17	1-Jan-00
28-Jan-14	27-Jan-21
1-Jan-18	31-Dec-21
19-Nov-20	21-Nov-21
4-Aug-19	30-Jul-21
1-Jul-16	30-Jun-21
21-May-20	21-May-21
1-Sep-17	1-Sep-20
15-Jan-20	15-Jan-21
20-Aug-13	20-Aug-11
1-Apr-19	1-Apr-20
1-Apr-19	1-Apr-20
31-Aug-20	31-Oct-23

[mahoneypat · Accepted solution]

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 · Accepted solution]

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.

[mahoneypat]

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

Regards,

Pat

[mahoneypat · Accepted solution]

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]

@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!