Date Format/Conversion

[srplesz]

I'm currently trying to calculate trailing 12 month financials and our financial data is coming through as a string in the following format "mmYYYY"
Therefore it doesn't sort properly. 
I'm having difficulties getting the date to convert to an actual date . 

Thanks!

[Hub119 · Accepted solution]

I would suggest the following steps... 1) Convert your MonthYear field to string if isn't already stored that way, 2) use the following function to get your data in the right starting format to correct for single digit months: "padleft([MonthYear],6,'0')" and finally 3) The function "DateTimeParse([MonthYear],'%m%Y')" will return you a properly formatted date data for the first of the given month which should allow you to properly sort.

[binuacs]

@srplesz 

IIF(length(toString([MonthYear]))<6,DateTimeParse('01'+PadLeft(toString([MonthYear]),6,'0'),'%d%m%Y'),DateTimeParse('01'+toString([MonthYear]),'%d%m%Y'))