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 31st 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.
There’s nothing you can’t do in Power BI from an analytical perspective.
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
