Power BI: Add a dynamic month column

In Power BI it is often useful to filter data on the current month, for example to keep monthly reports or analyses up to date. In this article you’ll learn how to add a dynamic month column in Power BI that automatically marks the current month. This makes it easier to build dashboards and reports that are always up to date without manual adjustments.

What you need

• A date table in your Power BI model with a column containing full dates

• Basic knowledge of DAX formulas in Power BI

Step 1: Check whether you have a date table

A date table is essential for time-based analyses in Power BI. Check whether your model contains a date table with a column in which every day of the year is listed. This table is used to create the dynamic month column.

If you do not yet have a date table, you can easily create one with the DAX function CALENDAR or CALENDARAUTO(). Add it via ‘Modeling’ > ‘New Table’ and use for example:

DateTable = CALENDARAUTO()

Step 2: Create a dynamic column for the current month

Add a new calculated column to your date table that indicates whether the date falls in the current month. Do this with the following DAX formula:

IsCurrentMonth = 
IF(
    YEAR(DateTable[Date]) = YEAR(TODAY()) && 
    MONTH(DateTable[Date]) = MONTH(TODAY()),
    "Yes",
    "No"
)

This column indicates for each date whether it falls within the current month with “Yes” or “No”. This makes it possible to easily filter on current data.

Step 3: Use the dynamic month column in your report

Use the new IsCurrentMonth column as a slicer or filter in your reports and dashboards. This allows you to quickly and automatically show the data for the current month without having to manually adjust filters.

This is especially useful for monthly reports, KPI overviews, and dashboards that must always be up to date.

Tips & common mistakes

• Date table as date source: Always use a date table and not date columns directly from your fact tables, to guarantee consistent time filters.

• Avoid static dates: Do not use fixed dates in your DAX, but always use dynamic functions like TODAY() for current analyses.

• Check time zone: The TODAY() function works based on the system date of the Power BI environment; keep this in mind for international reports.

Example: dynamic month filter in a sales dashboard

Suppose you have a sales dashboard where you want to show revenue for the current month. With the IsCurrentMonth column you can add a slicer so users automatically see the data for the current month. This prevents you from having to manually adjust filters every month and ensures an up-to-date overview.

Now you know how to add and use a dynamic month column in Power BI. You can apply these steps directly in the online environment or Power BI Desktop.

Our tip: Document every Power Query step with a clear name. This way a colleague (or yourself six months from now) immediately understands what is happening without having to read the formula.