Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

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

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.

1 ACCEPTED SOLUTION
Singaravelu_R
Resolver III
Resolver III

Hi Harika,

 

Please create a calcuate column 

Year=YEAR(Data[Date].[Date])
Month = MONTH(Data[Date].[Date])
 
Singaravelu_R_0-1633083202262.pngSingaravelu_R_1-1633083221392.png

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_2-1633083258961.png

 

View solution in original post

2 REPLIES 2
Singaravelu_R
Resolver III
Resolver III

Hi Harika,

 

Please create a calcuate column 

Year=YEAR(Data[Date].[Date])
Month = MONTH(Data[Date].[Date])
 
Singaravelu_R_0-1633083202262.pngSingaravelu_R_1-1633083221392.png

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_2-1633083258961.png

 

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))))))

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.