Forum Discussion

Userpath77's avatar
Userpath77
Icon for Helper II rankHelper II
2 years ago
Solved

Compare Month and Year from Date String

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.

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

3 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    2 years ago

    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

  • Userpath77's avatar
    Userpath77
    Icon for Helper II rankHelper II
    2 years ago

    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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.