Compare Month and Year from Date String

[Userpath77]

Hello All

I need a formula that compares two dates: Table1[Date] and Table 2[Date] and then returns "Late" if Table1[Date] is later than Table2[Date] or "Current" if Table1[Date] is the same as Table2[Date] or "Pending" if Table1[Date] has no value.

The issue is I need it to just look at month and year and not the day. As an example, 1 Oct 2023 and 15 Oct 2023 would be considered the same since month and year are the same. 1 Nov 2023 and 1 Oct 2023 would be "late" since month and year are different.

[Anonymous · Accepted solution]

Hi @Userpath77 ,

You can try below formula:

Status =
IF (
    HASONEVALUE ( Table2[Date] ),
    IF (
        ISBLANK ( MAX ( Table1[Date] ) ),
        "Pending",
        IF (
            YEAR ( MAX ( Table1[Date] ) ) = YEAR ( MAX ( Table2[Date] ) )
                && MONTH ( MAX ( Table1[Date] ) ) = MONTH ( MAX ( Table2[Date] ) ),
            "Current",
            IF ( MAX ( Table1[Date] ) > MAX ( Table2[Date] ), "Late", "Pending" )
        )
    ),
    BLANK ()
)

Best Regards,
Adamk Kong

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

[Userpath77]

Thanks Bhanu. I tried but the formula seems to only let me use measures for Current Date and Other Date. Both date fields are columns in two seperate tables. Any thoughts on how to modify?

[bhanu_gautam]

@User , You can try using below method

StatusColumn =
VAR CurrentDate = Table1[Date]
VAR OtherDate = Table2[Date]
RETURN
SWITCH (
TRUE (),
ISBLANK ( CurrentDate ), "Pending",
YEAR ( CurrentDate ) = YEAR ( OtherDate ) && MONTH ( CurrentDate ) = MONTH ( OtherDate ), "Current",
CurrentDate > OtherDate, "Late",
"Pending"
)

Please accept as solution and give kudos if it helps