By: Kenneth A. Omorodion | Updated: 2024-08-01 | Comments (1) | Related: > Power BI
Problem
Recently, I wrote about how to assign week identity to dates in a calendar table using DAX and how it is used in a Power BI report. In this article, I will explain another very similar and common requirement in reports building: how to set the current fiscal month identity in a calendar table when dealing with fiscal periods.
Solution
It is straightforward to identify the current month on a calendar table when your business dates start in January. However, when it comes to current fiscal months, it can be a bit tricky, particularly if your fiscal dates don't start in January.
To demonstrate this, I have created a sample dates table with a few columns and what we are trying to achieve on the dates table below.
In most cases, end users would like to use this current month identity column in the following ways:
- As values in a Slicer visual.
- In a filter pane.
- In a DAX measure for filtering; or
- In a DAX calculated column.
But how do we create the column for Fiscal Month identity using DAX? This article provides a step-by-step approach that makes it easy to achieve our goal.
Step 1: Create a Calendar Table If You Do Not Have One Already
There are different approaches regarding how a Calendar table can be created. For this article, I will use the approach seen in the DAX code below. For simplicity, I only included dates from June 1, 2024, to the current date of writing this blog post.
DatesTable 2 =
ADDCOLUMNS (
CALENDAR ( DATE ( 2024, 06, 01 ), TODAY()), --- Dates for only June 2024 to July 2024.
"Year", YEAR ( [Date] ),
"Month Num", MONTH ( [Date] ),
"Month Name", FORMAT ( [Date], "mmm" ),
"Week Name", COMBINEVALUES ( " ", "Wk", WEEKNUM ( [Date], 1 ) )
)
Please adapt your calendar date according to your business requirements. For instance, you don't need to use End Date as today. The output of the DAX code should look like the table below.
Step 2: Define the Current Fiscal Year
This is a calculated column, as seen in the code below, and is broken down in the explanation that follows.
Current Fiscal Year =
IF ( MONTH ( TODAY () ) >= 7, YEAR ( TODAY () ), YEAR ( TODAY () ) - 1 )
- This DAX code snippet sets the current fiscal year based on today's date.
- If today's month is July or later (month >= 7), the fiscal year is the current year.
- If today's month is before July, the fiscal year is the previous year.
The output of the DAX snippet is provided below.
Step 3: Define the Current Fiscal Month
This is another calculated column seen in the DAX code snippet below. I have also dissected the code to explain what it is doing.
Current Fiscal Month =
IF ( MONTH ( TODAY () ) >= 7, MONTH ( TODAY () ) - 6, MONTH ( TODAY () ) + 6 )
- This DAX code snippet sets the current fiscal month based on today's date.
- If today's month is July or later, the fiscal month is calculated as the current month minus 6.
- If today's month is before July, the fiscal month is calculated as the current month plus 6.
The output of this calculated column is shown below.
Step 4: Define the Fiscal Year for the Date in the Calendar Table
At this stage, we need to create another calculated column for the 'Date Fiscal Year'.
Date Fiscal Year =
IF (
MONTH ( 'DatesTable 2'[Date] ) >= 7,
YEAR ( 'DatesTable 2'[Date] ),
YEAR ( 'DatesTable 2'[Date] ) - 1
)
The above code can be explained as follows:
- This DAX code snippet sets the fiscal year for each date in the Calendar Table.
- If the date's month is July or later, the fiscal year is the year of the date.
- If the date's month is before July, the fiscal year is the previous year.
The output of this column can be seen below.
Step 5: Define the Fiscal Month for the Date in the Calendar Table
Like Step 4, we need to create a calculated column for the 'Date Fiscal Month' as seen in the DAX code snippet below.
Date Fiscal Month =
IF (
MONTH ( 'DatesTable 2'[Date] ) >= 7,
MONTH ( 'DatesTable 2'[Date] ) - 6,
MONTH ( 'DatesTable 2'[Date] ) + 6
)
The above DAX code can be explained as follows:
- This variable sets the fiscal month for each date in the Calendar Table.
- If the date's month is July or later, the fiscal month is calculated as the date's month minus 6.
- If the date's month is before July, the fiscal month is calculated as the date's month plus 6.
The output of the calculated column is as seen below.
Step 6: Check If the Date is in the Current Fiscal Month
In this step, we create one more column to identify the current fiscal month in the dates in the Calendar table. See the DAX code snippet below.
IsCurrMth =
IF (
'DatesTable 2'[Current Fiscal Year] = 'DatesTable 2'[Date Fiscal Year]
&& 'DatesTable 2'[Current Fiscal Month] = 'DatesTable 2'[Date Fiscal Month],
"Yes",
"No"
)
The DAX code above can be explained as follows:
- This DAX code snippet checks if the fiscal year and fiscal month of the current date match those of each date in the Calendar Table.
- If they match, it sets the value to "Yes" (indicating it's the current month), otherwise to "No".
The output of this stage is shown below.
Step 7: Add a Column for the Fiscal Month Identity
This last step needs to show if a row is "Current Month" or not on the Calendar table. See the code snippet below.
Fiscal Month Identity =
SWITCH(TRUE(), 'DatesTable 2'[IsCurrMth] = "Yes", "Current Month", 'DatesTable 2'[Month Name] )
The above DAX code snippet returns "Current Month" if
IsCurrMth is "Yes";
otherwise, it returns the month name from the 'DatesTable 2'.
The output is below.
It is essential to note that we can make this whole task more efficient by leveraging DAX variables rather than creating multiple columns. However, it is easier to trace your steps and understand what the final DAX is doing if we break down the process into multiple columns, as we have done throughout this article.
You can use or adapt the DAX code below to leverage variables in this task.
IsCurrMonth =
VAR _CurrentFiscalYear =
IF ( MONTH ( TODAY () ) >= 7, YEAR ( TODAY () ), YEAR ( TODAY () ) - 1 )
VAR _CurrentFiscalMonth =
IF ( MONTH ( TODAY () ) >= 7, MONTH ( TODAY () ) - 6, MONTH ( TODAY () ) + 6 )
VAR _DateFiscalYear =
IF (
MONTH ( 'DatesTable 2'[Date] ) >= 7,
YEAR ( 'DatesTable 2'[Date] ),
YEAR ( 'DatesTable 2'[Date] ) - 1
)
VAR _DateFiscalMonth =
IF (
MONTH ( 'DatesTable 2'[Date] ) >= 7,
MONTH ( 'DatesTable 2'[Date] ) - 6,
MONTH ( 'DatesTable 2'[Date] ) + 6
)
VAR _IsCurrMth =
IF (
_CurrentFiscalYear = _DateFiscalYear
&& _CurrentFiscalMonth = _DateFiscalMonth,
"Yes",
"No"
)
RETURN
SWITCH ( TRUE (), _IsCurrMth = "Yes", "Current Month", 'DatesTable 2'[Month Name] )
In summary, this article demonstrates how to create a Fiscal Month identity column on your usual Dates Table or Calendar Table. I have also mentioned some popular uses of these Fiscal Month identity columns on a dates table. These functionalities are very powerful in enhancing the look, feel, and usability of Power BI solutions. I am hoping it will be of great benefit to the community.
Next Steps
- Read more about the Power BI Dates Table in this Microsoft documentation.
- Read more on the YEAR DAX function.
- Read more on the MONTH DAX function.
- Read more on the DAX TODAY function.
- Read more on the SWITCH DAX function.
- Read more on DAX Variables.
Learn more about Power BI in this 3 hour training course.
About the author
Kenneth A. Omorodion is a Business Intelligence Developer with over eight years of experience. He holds both a bachelor’s and master’s degree (Middlesex University in London). Kenneth has the MCSA, Microsoft Data Analyst - Power BI and Azure Fundamentals certifications. Kenneth is a Microsoft Certified Trainer and has delivered corporate training on Power BI, SQL Server, Excel and SSRS.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2024-08-01
