Help in data logic

[MadhavTR]

I have Period (date) in a column which is of pattern 2025/003, 2024/009 (YYYY/0MM). I need to manipulate this data into the last day of that particular month, for example if 2025/003 then 31/03/2025 and so on. 

Need your help in the solution. TIA

[binu_acs · Accepted solution]

@MadhavTR 

DateTimeFormat(DateTimeTrim(DateTimePArse([Date],'%Y/0%m'),'LastofMonth'),'%d/%m/%Y')

[MadhavTR]

Thank you it worked.

[MadhavTR]

I have got 1 more variation now, how do I find out the exact same expression for 6 months prior. For example in certain use case if period = 2025/003 i need to get it as 30/09/2024 instead of 31/03/2025

[binu_acs]

@MadhavTR Try the below formula

DateTimeFormat(DateTimeAdd(DateTimeTrim(DateTimePArse([Date],'%Y/0%m'),'LastofMonth'),-6,'month'),'%d/%m/%Y')