Modelling a fixed or variable custom financial year in Power BI
Whether your financial year ends on a fixed date every year or on the first Monday in June, this blog will show you how to incorporate it into your calendar table.

Posted by Sam Lowrie on 17 May 2019

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Setting a custom financial year end in a Power BI Calendar

This blog works on the assumption that you already have a date calendar table (if you don't, check out this blog).

Your date table probably looks something like this:

Calendar table

A basic calendar table, which could do with some extra non-standard columns!

Setting a fixed financial year start

Financial years vary from company to company, but normally there is a set day when the financial year starts. One way to set this is to create a calculated column in your calendar table:

Adding calculated column

Right-click on your date table to insert a calculated column.

 

You can then enter a formula (the example below assumes that your financial year starts on the 1st of April):

Financial year =

-- get the current row's year

VAR RowsYear = YEAR('Date Table'[Date Key])

-- get the date that is the start of the financial year (here 1st April)

VAR FirstDay = DATE(RowsYear,4,1)

-- if this date hasn't yet passed in the current year, return

-- that year; otherwise, return the current year plus 1

VAR FinancialYear= IF(

'Date Table'[Date Key] < FirstDay,

RowsYear,

RowsYear + 1

)

RETURN FinancialYear

This version works if the start of the year is always fixed to the same day. It can be any day (except perhaps for the 29th of February). 

You can change the values within the DATE function to alter when the new financial starts, and check that it crosses over at that point:

Financial year changing

Regardless of the year, after the 1st of April the financial year will change to the following year.

Creating a variable financial year start

What if the first day of your financial year varies (say your financial year begins on the first Monday in April, for example)? The process to model this begins in the same way - create a calculate column:

Variable financial year =

-- capture the current row's year

VAR RowsYear = YEAR('Date Table'[Date Key])

We need the date table to only contain one value for each row (the first Monday in April of that row's year):

VAR SpecifyDate = FILTER(

-- remvoe any existing filters

ALL('Date Table'[Date Key]),

-- only dates in the current row's year

YEAR('Date Table'[Date Key]) = RowsYear

-- choose the month of interest

&& MONTH('Date Table'[Date Key]) = 4

-- include the first 7 days, as 1 of these will be a Monday

&& DAY('Date Table'[Date Key]) <= 7

-- weekday returns 1-7 as Sun-Mon; the 2 sets Monday=1

&& WEEKDAY('Date Table'[Date Key],2) = 1

)

This captures for each year the date of the first Monday in April but can be adapted by changing the filter values. 

Finally we need to check if the current row's date has passed the financial year start just calculated, and assign a year:

-- if that date hasn't passed in the current year, return that year;

-- otherwise, return the current year plus one

VAR FinancialYear = IF(

'Date Table'[Date Key] < SpecifyDate,

RowsYear,

RowsYear + 1

)

RETURN FinancialYear

This will return the current year for values before the calculated financial year start or the year plus 1 for those after it.  This results in the financial year being different even within the same month:

Variable financial yeara.

The first Monday in April of 2010 was the 5th so this date returns 2011 as its financial year.

Now you have the start of the year, you can calculate things like day or week cycles - if you are interested check out this blog.

This blog has 0 threads Add post