cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hpatel247 Regular Visitor
Regular Visitor

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

Accepted Solutions
Highlighted
Super User
Super User

Re: Start and end dates for fiscal years

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

 

ThomasFoster Established Member
Established Member

Re: Start and end dates for fiscal years

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.

Super User
Super User

Re: Start and end dates for fiscal years

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

9 REPLIES 9
Rfranca Member
Member

Re: Start and end dates for fiscal years

Hi @hpatel247

 

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

ThomasFoster Established Member
Established Member

Re: Start and end dates for fiscal years

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

hpatel247 Regular Visitor
Regular Visitor

Re: Start and end dates for fiscal years

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

 

hpatel247 Regular Visitor
Regular Visitor

Re: Start and end dates for fiscal years

Hi @ThomasFoster

 

I am using DAX.

 

kind regards

 

Hetal

Highlighted
Super User
Super User

Re: Start and end dates for fiscal years

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

 

hpatel247 Regular Visitor
Regular Visitor

Re: Start and end dates for fiscal years

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

ThomasFoster Established Member
Established Member

Re: Start and end dates for fiscal years

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.

hpatel247 Regular Visitor
Regular Visitor

Re: Start and end dates for fiscal years

Hi @ThomasFoster 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

Super User
Super User

Re: Start and end dates for fiscal years

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