In this tutorial, I’ll discuss how to display information based on financial year within Power BI reports. However, this analysis is a bit complex. This is because it has different customers and each of them has a different financial year. **You can watch the full video of this tutorial at the bottom of this blog.**

I have made up an example where I can select different customers. When I select different customers here, they all have different financial years. Some of them might be between **March 2020 to February 2021 **or **July 2020 to June 2021**.

I can’t just utilize the **Financial year **column in the **Date** table to filter them by financial year (**fiscal year**). So, by clicking through our customers, we need to dynamically show the fiscal year for that particular customer.

In this example, I slimmed down the **Date table**. So, this displays data by day instead of by month and year.

## Analyzing The Data Model

Now, let’s have a look at the data model where I need to solve this particular scenario. This contains customers (Customer_List), dates (Dates), and our customer data (**Raw_Data**).

In this example, the **Raw_Data** table has **Date**, **Customer ID**, and **Amount **columns.

The **Customer_List** table is where I can set up a parameter that shows the starting month of the customer’s financial year. This table contains **Customer ID**, **Customers**, and the **FY Start Month** columns.

The **FY Start Month **column is where I can put a number as a metric of when the financial year started in a particular month. Hence, **Google** is in March, **Microsoft** is in June, and **Salesforce** is in September.

This might look like I have to do something complex such as creating **multiple date** tables or multiple financial year columns in one date table. But I’m going to use a formula with a simple setup.

## Filtering A Specific Financial Year

The key thing to note here is that instead of doing anything in the **Dates** table, I created a new table called **Financial Years**.

I created this table because I need to **capture a value based on a selection** here.

The **Financial Years** table is quite simple as it only has the **Fiscal Year** and the **Fin Yr** column. I consider this as the **supporting table**.

I’m going to use some of the selections or metrics that are within this table in a formula to create a dynamic calculation.

Keep in mind that the **Fiscal Year** slicer here is coming from the table that I created (**Financial Years**). It has no relationship to the data model.

By default, the **Total Amount by Date** visualization is displaying everything.

On the other hand, the **Amounts by FY by Date** displays the results based on the selections from the slicers. In this example, it only displays the financial year 2020 (**FY20**) for **Microsoft**.

So, how can I achieve that dynamic financial year calculation results in the **Amounts by FY** **by Date** visualization?

## Dynamic Financial Year Filtering

In order to display a dynamic financial year result, I created some calculations where I can dynamically find the **Start Date** and **End Date** when having selections via the **Customers **and **Fiscal Year **slicers.

If I can find that, then I can **dynamically blank out all the dates that are outside of that start and end date**.

Bear in mind that if you can blank something out, then **it’s not going to appear in your visualization**.

This particular formula is one of the techniques on how to do that calculation.

Before I proceed with the step-by-step discussion of the formula, I’ll duplicate the **Total Amount by Date** visualization first and display it as a table. I also added the **Amounts by FY** table here so I can easily show you what I’m trying to do.

Then, I’ll sort the **Date** column here and set it to “**Show items with no data**”.

If I scroll down in this table, you’ll see that the **first data point **in this particular month for **Microsoft’s financial year** is on **June 7, 2020**.

Now, I’ll change the selection in the **Customers** slicer to **Salesforce**.

You’ll then see that the dates have changed. Noticeably, the **FY Start Date** is now **September 1**, which is the start date of the financial year for **Salesforce **in this particular example.

So, how did I create that **Start date** and **End date** calculations?

### Start Date Measure

Let’s now analyze the start date (**FY Start Date**) measure.

In this formula, I’m trying to find the inputs for the start date. I used the **DATE** function to get the year (**FiscalYearSelected**) and the start month (**FinancialStartMonth**).

When I’m selecting a customer, I have a column that has the financial year start month (**FY Start Month **column) under the **Customer_List table**. Basically, I’m just capturing that information here.

Remember that in the supporting table (**Financial Years** table) I created, I had a column that returns the actual year (**Fin Yr **column). So, I can just select an option from the **Fiscal Year** slicer.

Then, I can easily capture that year’s value here.

After that, I’ll input the **FinancialStartMonth **and** FiscalYearSelected** within the **DATE** function here.

I know that the start date is going to be the first day of the month. So, I can just put **1** here.

And that returns the start date of the selected financial year.

### End Date Measure

Now, let’s take a look at the **FY End Date** measure.

As you can see, it’s similar to the **FY Start Date** measure. The only difference is that I used **FiscalYearSelected + 1 **in the formula. With this, if the selected financial year (**FiscalYearSelected) **is 2020, then the year for the **FY End Date** will be **2021**.

Here, you’ll notice that this isn’t actually the first date of the next financial year. In the actual calculation, this should be **August 31, 2021**.

In the formula, we can also use the relational operator which is **less than (<)**. If we use **less than (<)**, it’s always going to be **one day less than the fiscal end date**. And that is fine because **the result is going to be the actual last date**.

In this particular example, since the fiscal year-end date is** September 1, 2021,** and we use **less than (<)**, the actual last date will be the **31 ^{st} of August**.

### Amount By Financial Year Measure

Let’s then analyze the next formula which is the **Amount by FY** measure.

This is also quite similar to the previous two formulas. However, instead of breaking down those sets of formulas, I’ve put them into this single formula using variables.

This is the most important part of the formula.

Take note that in this particular example, the first data point or the start date of Salesforce for the financial year 2020 is **September 4, 2020**.

Basically, this part of the formula indicates that if the **MIN** date (**September 4, 2020 **for this example) is greater than or equal to the **StartDate** (**September 1, 2020**), and it’s also less than the **EndDate **(**September 1, 2021**), then it will display the **Total Amount**. If not, then it will be blank.

This section calculates the **Total Amount** that will be displayed under the **FY End Date** of this model.

Then, the **Total Amount** is filtered here using the **FILTER** function. Therefore, it’s just displaying the **Total Amount** with the date that is within the range of the **StartDate** and **EndDate**.

With this logic, it enables me to dynamically display the **amount by financial years **here as I select options from the **Customers** and **Fiscal Year **slicers.

******* Related Links ******* **Power BI Financial Year To Date Calculations****How To Create Unique Financial Year Quarters Using A Calculated Column****Sorting Dates By Financial Year In Power BI**

## Conclusion

This tutorial covered some interesting techniques on how to dynamically display different financial years within Power BI reports. The example actually came from a real scenario that a customer of ours has experienced.

The given example is of a more complex nature. But you can always solve it with Power BI.

You just need to understand the importance of **using variables**, **setting up your data model correctly**, **using supporting tables**, and then **working out a logic **that enables you to create a dynamic calculation and visualization.

Check out the links below for more examples and related content.

All the best,

Sam

[youtube https://www.youtube.com/watch?v=1CXKCQn9b04?rel=0&w=784&h=441]