How to Convert FY24 Aug to a date format 01/08/2023?

[sanjyot_firke]

We have data coming in year as FY24 and month as Aug. How do we convert that to 01/08/2023?

FY is financial year  and the year starts from July and ends in June.

Regards,

Sanjyot

[SamInogic · Accepted solution]

Hi @sanjyot_firke , 

As per our understandings you are looking for date change from FY year which starts in the July and ends in June, 

Initially We have to create the FYDate using below DAX Expression,

FY_Date =

var FormatedYear = RIGHT(LEFT(Table4[Date],4),2)

var FormtatedMonth = Right(Table4[Date],3)

var UpdatedMonth = SWITCH(FormtatedMonth,

"Jan","07","Feb","08","Mar","09","Apr","10","May","11","Jun","12","July","1","Aug","02","Sep","03","Oct","04","Nov","05","Dec","06")

var UpdatedDate = CONCATENATE(CONCATENATE(UpdatedMonth,"/"),CONCATENATE("01/",CONCATENATE("20",FormatedYear)))

return UpdatedDate

And Need to change the data-type to Date and format as dd/mm/yyyy,

There after we have create one more column that will be Resultant column,

Result =

Var FyMonthNo = Table4[FY_Date].[MonthNo]

var standardMonthno = IF(FyMonthNo<6,FyMonthNo+6,IF(FyMonthNo>6,FyMonthNo-6))

var standardYear = IF(FyMonthNo<6,Table4[FY_Date].[Year]-1,IF(FyMonthNo>6,Table4[FY_Date].[Year]))

var UpdatedDate = CONCATENATE("01/",CONCATENATE(CONCATENATE(standardMonthno,"/"),standardYear))

return UpdatedDate

You can change the Data-Type and formatting for this column as well, 

Thanks!

Inogic Professional Services Division

Power Platform and Microsoft Dynamics 365 CRM Development – All under one roof!

Drop an email at crm@inogic.com

Services:  http://www.inogic.com/services/

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/