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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hpatel247
Helper I
Helper I

Start and end dates for fiscal years

Hi All,

 

I have created a calendar table that consist of dates ranging 800 days before today and 800 days after today. I have then created calculated columns that show me the following:

 

MonthName (i.e January, February, March etc)

Fiscal Year (Our fiscal year runs from April to March so if date is 20/06/2016 the fiscal year column is 2017)

Fiscal Month (April will start as 1 and March will be 12)

Financial Year (shown as 2016-2017, 2017-2018 etc based on above information)

 

What i now want to know is if there is a way to create calculated columns that show the start date of the fiscal year and the end date of the fiscal year based on a date. e.g if the date is 6/11/2017, i want the start date to be 01/04/2017 and the end date to be 31/03/2018 and if date was 06/07/2018, the start date would be 01/04/2018 and the end date 31/03/2019.

 

Is this possible? Any help is much appreciated.

 

kind regards

 

Hetal

3 ACCEPTED SOLUTIONS
Stachu
Community Champion
Community Champion

there is STARTOFYEAR and ENDOFYEAR functions, which can calculate custom fiscal year
https://msdn.microsoft.com/en-us/query-bi/dax/startofyear-function-dax
e.g.

Measure = STARTOFYEAR('Calendar'[Date],"03-31")

 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

View solution in original post

Anonymous
Not applicable

As stachu says in their post, to add a column with the start and end of a fiscal year in a DAX table just add a column with the functions STARTOFYEAR and ENDOFYEAR.

 

One thing to add, is that when you are entering the string to specify the end of the fiscal year I would use a format that is obvious between date formats. Eg use "31 March" instead of 03/31 as you risk the report not working if run in different date format cultures.

View solution in original post

Stachu
Community Champion
Community Champion

it returns 26/06/2016 beacause it's the first date in the Calendar table (I assume)
If you want the formula to work you actually would need to have the start of the year in your table, so instead of going 800 days back it would need to go to Apr 1st of the same year where 800 day prior happens (so 01/04/2016 in your case)
you can then have other flag marking the +-800 days period if needed



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

View solution in original post

9 REPLIES 9
Stachu
Community Champion
Community Champion

there is STARTOFYEAR and ENDOFYEAR functions, which can calculate custom fiscal year
https://msdn.microsoft.com/en-us/query-bi/dax/startofyear-function-dax
e.g.

Measure = STARTOFYEAR('Calendar'[Date],"03-31")

 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Anonymous
Not applicable

As stachu says in their post, to add a column with the start and end of a fiscal year in a DAX table just add a column with the functions STARTOFYEAR and ENDOFYEAR.

 

One thing to add, is that when you are entering the string to specify the end of the fiscal year I would use a format that is obvious between date formats. Eg use "31 March" instead of 03/31 as you risk the report not working if run in different date format cultures.

Hi @Anonymous and @Stachu

 

Thank you for you help in this. It was me being silly when calculating the start of year as instead of putting 03-31 or 31 March, i was thinking it needed to be 01-04 or 01 April.

 

Now i have changed it, it works. However, as mentioned in my previous post, my earlier date starts from 26/06/2016 and this same date is shown on my start of year column. Is there a way to update the formula that will alway stay as 01/04 & year.

 

The same issue occurs with the end of year when the last date i have is 12/11/2020 so the end of year is shown as 12/11/2020. Just thinking whether this would affect my calculations if i refer to the start and end of year dates.

 

kind regards

 

Hetal

Hi @Stachu

 

I have tried that but does not work for startofyear.

 

Because my calendar lists dates that are 800 days before and after today, the earliest date i have is 26/06/16 and for startofyear, the date shown is 26/06/2016. When the dates change on 01/01/2017, the startofyear is shown as 05/01/2017.

 

the DAX formula is used is Start of FY = STARTOFYEAR(Dates[Date],"04-01")

 

kind regards

 

Hetal

Stachu
Community Champion
Community Champion

it returns 26/06/2016 beacause it's the first date in the Calendar table (I assume)
If you want the formula to work you actually would need to have the start of the year in your table, so instead of going 800 days back it would need to go to Apr 1st of the same year where 800 day prior happens (so 01/04/2016 in your case)
you can then have other flag marking the +-800 days period if needed



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Rfranca
Resolver IV
Resolver IV

Hi @hpatel247

 

YOU SHOULD USE A CALENDAR TABLE AND MAKE IT CHANGES TO THE FISCAL YEAR.

Hi @Rfranca

 

That is what i am having difficulty with. i don't know how the measure would look like

 

I have all my dates in a column and now need to create 2 calculated columns that show the start and end dates per financial year based on my dates.

 

kind regards

 

Hetal

 

Anonymous
Not applicable

Are you making your date table in the query editor or in DAX? It is possible in both.

Hi @Anonymous

 

I am using DAX.

 

kind regards

 

Hetal

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.