top of page
Search

Financial Calendar in Power BI

Updated: Jul 9




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

  1. Open Power BI Desktop.

  2. Go to the Modeling tab.

  3. Click on New Table and enter the updated DAX formula above.

  4. 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.

  1. 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.

  1. Use FinancialMonthName in Reports:

  • Drag and drop the FinancialMonthName column into your visuals to display month names instead of numbers.


 

32 views0 comments

Comments


bottom of page