Hi @ashwinkolte
If you want to return the last day of the year/month of 'Calendar'[Date] in a calculated column, I would instead suggest:
End of month =
EOMONTH ( 'Calendar'[Date], 0 )
The ENDOFMONTH function is one of the time intelligence functions that normally used for modifying filters within CALCULATE/CALCULATETABLE.
It returns a 1x1 table which contains the last day that exists in that column in the same month/year of the maximum date in the filter context.
Behind the scenes the date column reference is actually converted to
CALCULATETABLE ( DISTINCT ( 'Calendar'[Date] ) )
See https://dax.guide/ENDOFMONTH/ for more info.
ENDOFMONTH (and other time intelligence functions) are restricted to returning dates that actually exist in the referenced column, so my guess is that the maximum value of 'Calendar'[Date] in July 2023 is 1-July-2023, while the max value in July 2022 is 31-July-2022. You could also solve your issue by ensuring 'Calendar' contains complete calendar months (which is best practice in general).
Regards
