Create a Date Dimension in Power BI in 4 Steps – Step 2: Fiscal Columns

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

2017-09-06_11h57_45

In step 1 of creating date dimension I explained how to create the base query with calendar columns. In this post I will explain how to add fiscal columns calculated in the date dimension. Many of business reports generates on fiscal year, fiscal quarter, and fiscal period, so having fiscal columns is important part of a date dimension. For creating the calculation for fiscal columns, I will use generic calculations that works perfectly for all scenarios (when you have any particular month as start of fiscal year). To learn more about Power BI; read Power BI book from Rookie to Rock Star.

Prerequisite

For this post you need to complete the step 1 of creating date dimension.

Parameter for Fiscal Year Star

Because I want the configuration to be dynamic, and I want to be able to change the fiscal year start to any month and it works perfectly fine, so I am going to create a parameter for fiscal year start. In Query Editor, create a new parameter. Name it StarOfFiscalYear. this will hold the month value which is start of fiscal year.

2017-09-06_10h15_23

After creating the parameter, then we can start applying the logic for every fiscal column as a new custom column.

Creating Fiscal Columns

We do not have built-in functions to calculate fiscal columns. However, calculations are easy. We will go through it one by one, and you will see how the logic can be implemented.

Fiscal Year

The first and easiest one is to calculate Fiscal year. Consider that months 7 (July) is start of fiscal year, then we should have this;

2017-09-06_10h20_28

As you can see in the image above; June 2017 considered as fiscal year 2017. However, July 2017 is part of fiscal year 2018. So the simple logic can be like this:

if (calendar month >= fiscal year start)

then fiscal year = calendar year

else fiscal year = calendar year + 1

This code is pseudo code. don’t write that exactly in M! Let’s now implement it in M;

Create a Custom column in Date Dimension Query (I’ve explained how to create that query in previous post),

2017-09-06_10h26_12

In a custom column you can write your M code as below;

2017-09-06_10h27_52

This is the M version of the same logic you’ve learned above.

After creating every custom column, make sure that you change its data type to proper one. for this; change it to Whole Number.

Fiscal Period or Month

calculating fiscal period or month is following the same IF expression logic. but different calculation this time. here are some samples;

2017-09-06_10h30_57

So as an example; June 2017 is Fiscal period 12 of the fiscal year 2017. July 2017 is fiscal period 1 of the fiscal year 2018. As you can see the logic above; if calendar month is before fiscal year start month, then we add 6 to the calendar month to get the fiscal year, otherwise; we’ll reduce 6 from it.

What is 6? where this value is coming from? let’s look at another example;

In this example; assume start of fiscal year is April (month 4);

2017-09-06_10h37_18

Well, that made it more difficult. Didn’t? now we have two numbers; sometimes plus 9, sometimes minus 3! Don’t be afraid the logic is simple. Here it is the logic;

If the calendar month is greater than or equal to fiscal year start, then we can simply say; Calendar Month – (Fiscal Year Start – 1)

if otherwise; we can say; Calendar Month + (12 – Fiscal Year Start + 1)

So, is how to implement it;

Create a new Custom Column for Fiscal Period. put the code below there;

2017-09-06_11h00_33

Fiscal Quarter

Calculating fiscal quarter should be easy when you have the fiscal period. You can start with dividing by 3. Then Round the value Up to get the fiscal quarter. Here is how to do it;

Click on Fiscal Period Column (calculated from previous step), then go to Add Column, from Number Column, select Divide under Standard;

2017-09-06_11h05_56

Divide it by 3 (number of months in each quarter);

2017-09-06_11h07_02

Result will be a column with values that in some cells has decimal places. we need to Round Up this. Click on this column, go to Transform tab, under Number transformation from Rounding, select Round Up.

2017-09-06_11h08_11

Note that this last transformation is coming from Transform Tab to avoid extra column creation. after this change, then rename the column to Fiscal Quarter;

2017-09-06_11h09_46

Fiscal Week

Fiscal week calculation is a bit different from other calculations. Not all business work on fiscal week basis, for example a business on football league or sports league would work on a week basis. This has some varieties. one of them is that a business can say their fiscal year starts at the very first Monday of July each year. another company might say they have a per-defined date as the start of fiscal year. So, depends on the business, the logic is different. The example that I showed you so far is a generic example. Because Fiscal week calculation itself is quite a bit of topic. I’m going to explain that in another post.

Code of this example

The whole script of M for Date dimension in this example is as below;

Summary

In summary you’ve seen how you can add fiscal year, period, and quarter to a date dimension. examples used in this post are generic. You can use these calculations in any type of date dimension. Fiscal week calculation is different, and is not generic for everyone, I’ll write a blog post separately for that. I will talk about adding public holidays to this date dimension in next post. So stay tuned.

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
Reza Rad
Reza Rad is an Author, Trainer, Speaker and DW/BI Consultant. He has a BSc in Computer engineering; he has more than 15 years’ experience in databases, programming and development mostly on Microsoft technologies. He is a Microsoft MVP in Data Platform for seven continues years (from 2011 till now) for his dedication in Microsoft BI. He is author of some SQL Server and BI books, and also Power BI online book; from Rookie to Rock Star.

2 Comments

Leave a Reply to jw Cancel reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">