Skip to main content
Power BI Interview Questions

An easy way to add Financial Year into Date Table in Power BI

Table of Contents

When it comes to finance the calculation of financial year is a simple but most crucial task as Indian financial year starts from 1st April to 31st March. So, while doing financial analysis we have to add column to calculate financial year in the Date table.

Let’s First Learn How to Create a Date Table in any Data Model

Date Table is crucial in a data model while working on time-based data like sales, purchases, website traffic over a period to analyze patterns.

We can easily add a date table using DAX function or M Language in Power Query. We will see how to do it using DAX functions.

				
					DateTbl = CALENDAR (
        MAX (FabricData [Month]),
        MAX (FabricData [Month])
        )
Let’s break down the formula:
  1. DateTbl: Name of the Table.
  2. Calendar (): It takes 2 parameters start date and end date.
  3. Min (FabricData [Month]): it will take the beginning date from the month column which holds dates in the fabric table.
  4. Max (FabricData [Month]): it will take the end date from the month column which holds dates in the fabric table.

Now let’s look at the financial year calculation

We have this date table of finance data set. As You can see there is a date column in which dates are entered in the form of DD/MMMM/YYYY and a FY column which is telling us that in which particular financial year that date belongs to.

Financial Year

How to Add New Column to Date Dimension Table in Power BI

Step 1: Go to Home Tab

Step 2: Go to Calculations Section

Step 3: Click on New Column Button (Shown in the image below)

Add New column Ribbon

Step 4: A bank column and a Blank formula bar will appear where you will have to write the DAX expression.

Financial Year Calculation Formula

Now in the formula bar we must write a DAX Functions which calculates the financial year. The logic would be if in the year 2017 the month is or before march then the financial year would be 16-17 and if the month is April or after April then it will be 17-18.

We have written the formula for this:

				
					FY = IF (
        MONTH (DateTbl [Month]) <= 3,
        (LEFT (DateTbl [Year], 4) -1 & "-" & right (DateTbl [Year], 2)),
        Left (DateTbl [Year] ,4) & "-" & right (DateTbl [Year], 2) +1)
            )

Let’s Understand what this Formula is saying.

FY is the name of the column. As we know IF function has 3 parameters.

= IF (Logical Test, Result if true, Result if false)

    1. Logical test: Now we have written that go to the Month column and if it is less than equal to 3.
    2. Result if true: If it is before march then go to the Year Column and Take the 4 digits from the left side (as we want the YYYY – YY format in the FY column) and deduct 1 and add Hyphen symbol (-) in and again go to the year table take 2 digits from the right side. It will happen if the condition is true.
    3. Result if false: If the condition is wrong then the month is obviously greater than 3 so we are asking power bi to go to the year column and take last 4 digits from year, add a hyphen and at last go to the year column again take the last 2 digits and add 1 in that.

Output

You have to write the DAX Expression in the formula bar as you can see below.

As soon as you hit the enter you will see values in the Blank Column, and as we can see in the row where date is 31st March, financial year is 2017-18 and on 1st April its 2018-2019 as we expected.

Financial Year Formula and Output

Now you are ready to do the financial analysis with correct financial year.

Add comment