How to get Fiscal year end date based on today's date

[Anonymous]

Hi All,

I have a column which holds today's date.Now, my requirement is to generate a Fiscal year end date in new column based on Today's date.

In my data fiscal year starts from 1st April to 31st March.

Ex:Today's date is 01/10/2021 based on that date i have to generate a column which should show fiscal year end date that is 31/03/2022.

Can someone help on this.

Thanks in advance.

[Singaravelu_R · Accepted solution]

Hi Harika,

Please create a calcuate column 

Year=YEAR(Data[Date].[Date])

Month = MONTH(Data[Date].[Date])

For fiscal year use below calculated column 

Fiscal Year = if((Data[year]=YEAR(TODAY()) && Data[Month]>=4), DATE(year(TODAY())+1,3,31),if((Data[year]=YEAR(TODAY())+1 && Data[Month]<=3), DATE(year(TODAY())+1,3,31)))

[Singaravelu_R]

Fiscal Year End1 = if((Data[year]=YEAR(TODAY()) && Data[Month]>=4), DATE(year(TODAY())+1,3,31),if((Data[year]=YEAR(TODAY())+1 && Data[Month]<=3), DATE(year(TODAY())+1,3,31),if((Data[year]<> YEAR(today()) && Data[Month] >=4),DATE(YEAR(Data[Date].[Date])+1,3,31),if((Data[year]<> YEAR(today()) && Data[Month] <=3),DATE(YEAR(Data[Date].[Date]),3,31),if((Data[year]=YEAR(TODAY()) && Data[Month]<=3), DATE(year(TODAY()),3,31))))))