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

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.

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;

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

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

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;

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

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;

### 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;

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

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.

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;

### 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.

## Video

Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.

## 5 thoughts on “Create a Date Dimension in Power BI in 4 Steps – Step 2: Fiscal Columns”

• jw says:

eagerly awaiting your “fiscal week” article, I’ve been trying to figure that out for months…

• James C Brogan says:

Awesome, step-by-step for us newbies! I really appreciate the help.

• Mike says:

How do I ensure that my start year is custom and not january. I specified this in the Start Date but when adding columns it does not work.

Thanks

• Hi Mike
If you want to have a custom start date anytime in the year. you can start building the dimension with a start DATE variable. and set it to whatever date you prefer, and start right from that date.
Cheers
Reza