This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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 15 May 2019

In this blog

Setting a fixed financial year start
Creating a variable financial year start

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:

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:

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:

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:

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.

Some other pages relevant to the above blog include:

This blog has 0 threads

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

Setting a custom financial year end in a Power BI Calendar

Setting a fixed financial year start

Creating a variable financial year start

Head office

London

Manchester