Financial Calendar in Power BI
- Alok Singh
- Jul 8, 2024
- 2 min read
Updated: Jul 9, 2024
DAX formula for getting Financial Calendar in Power BI. This will allow you to use the month names aligned with your fiscal year starting in April and ending in March in your reports and visuals.
DateTable =
VAR BaseCalendar =
CALENDAR ( DATE ( 2013, 4, 1 ), DATE ( 2023, 3, 31 ) ) -- Adjust the date range as needed
RETURN
ADDCOLUMNS (
BaseCalendar,
"Year", YEAR ( [Date] ),
"Month", MONTH ( [Date] ),
"Day", DAY ( [Date] ),
"MonthName", FORMAT ( [Date], "MMMM" ),
"YearMonth", FORMAT ( [Date], "YYYY-MM" ),
"Quarter", "Q" & ROUNDUP ( MONTH ( [Date] ) / 3, 0 ),
"FinancialYear", IF ( MONTH ( [Date] ) >= 4, YEAR ( [Date] ), YEAR ( [Date] ) - 1 ),
"FinancialMonth", IF ( MONTH ( [Date] ) >= 4, MONTH ( [Date] ) - 3, MONTH ( [Date] ) + 9 ),
"FinancialMonthName", SWITCH(
TRUE(),
MONTH([Date]) = 4, "April",
MONTH([Date]) = 5, "May",
MONTH([Date]) = 6, "June",
MONTH([Date]) = 7, "July",
MONTH([Date]) = 8, "August",
MONTH([Date]) = 9, "September",
MONTH([Date]) = 10, "October",
MONTH([Date]) = 11, "November",
MONTH([Date]) = 12, "December",
MONTH([Date]) = 1, "January",
MONTH([Date]) = 2, "February",
MONTH([Date]) = 3, "March"
),
"FinancialQuarter", "Q" & ROUNDUP ( IF ( MONTH ( [Date] ) >= 4, MONTH ( [Date] ) - 3, MONTH ( [Date] ) + 9 ) / 3, 0 )
)
Steps to Implement
Open Power BI Desktop.
Go to the Modeling tab.
Click on New Table and enter the updated DAX formula above.
Mark the Date Table as a Date Table:
Select the Date Table.
Go to the Modeling tab.
Click on Mark as Date Table and select the Date column.
Create Relationships:
In the Model view, drag the Date column from the Date Table and drop it onto the corresponding date column in your existing table.
Configure the relationship as necessary.
Use FinancialMonthName in Reports:
Drag and drop the FinancialMonthName column into your visuals to display month names instead of numbers.
Comments