topic Compare Month and Year from Date String in DAX Commands and Tips

Compare Month and Year from Date String

Userpath77 — Wed, 24 Jan 2024 16:13:40 GMT

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.

Re: Compare Month and Year from Date String

bhanu_gautam — Wed, 24 Jan 2024 16:41:16 GMT

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

Re: Compare Month and Year from Date String

Userpath77 — Wed, 24 Jan 2024 17:21:13 GMT

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?

Re: Compare Month and Year from Date String

Anonymous — Thu, 25 Jan 2024 06:30:30 GMT

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.