Start and end dates for fiscal years

[hpatel247]

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

[Stachu · Accepted solution]

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

[Anonymous · Accepted solution]

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.

[Stachu · Accepted solution]

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

[Anonymous · Accepted solution]

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]

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

[hpatel247]

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 · Accepted solution]

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

[Rfranca]

Hi @hpatel247

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

[hpatel247]

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]

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

[hpatel247]

Hi @Anonymous

I am using DAX.

kind regards

Hetal