Graphed LogoGraphed
Graphed LogoGraphed
How It WorksAgentsIntegrationsPricing

How to Create a Rolling Calendar in Power BI

Cody Schneider••7 min read

Tired of manually updating your Power BI reports every week just to see the last 7, 30, or 90 days of data? Creating a rolling calendar lets you build dynamic, self-updating dashboards that always show the most relevant time frame. This article will walk you through the exact steps to create a versatile rolling date calendar in Power BI using DAX, so you can build reports that work for you, not the other way around.

GraphedGraphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Watch Graphed demo video

Why Bother with a Rolling Calendar?

Standard date filters are static. If you filter for the "current month," that report will be useless on the first day of the next month until you manually change it. A rolling, or relative, calendar is different. It’s anchored to today’s date, so a “Last 30 Days” view will always show the last 30 days, whether you're looking at it on a Tuesday in June or a Sunday in October.

Here’s why it's a game-changer:

  • Always-On Relevance: Your reports are always current, showing performance over recent periods like the "Last 7 Days" or "Last 90 Days" without any user input. This is perfect for daily stand-ups or weekly performance reviews.
  • True "Like-for-Like" Comparisons: It allows you to build powerful comparative metrics like "Last 30 Days vs. Previous 30 Days" or tracking a Trailing Twelve Month (TTM) sales figure.
  • Reduces Manual Work: Stop building "this week's report." Instead, build one dynamic dashboard that automatically shows the latest data, freeing you up to focus on analysis rather than admin.

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Step 1: Build a Solid Foundation with a DAX Calendar Table

Before you can add any rolling logic, you need a proper, dedicated calendar table. Relying on the default date hierarchies from your data tables can cause problems down the line. A separate calendar table is a Power BI best practice because it gives you a clean, complete set of dates to work with.

We’ll create this using Data Analysis Expressions (DAX).

  1. In Power BI Desktop, navigate to the Data view (the small table icon on the left-hand navigation pane).
  2. In the ribbon at the top, select the Table tools tab, and click New table.
  3. A formula bar will appear. Copy and paste the following DAX code into it and press Enter.

Calendar Table = ADDCOLUMNS ( CALENDARAUTO(), "Year", YEAR ( [Date] ), "Quarter Number", QUARTER ( [Date] ), "Quarter", "Q" & QUARTER ( [Date] ), "Month Number", MONTH ( [Date] ), "Month Name", FORMAT ( [Date], "mmmm" ), "Month Abbreviation", FORMAT ( [Date], "mmm" ), "Day of Week Number", WEEKDAY ( [Date], 2 ), // 2 makes Monday = 1 "Day of Week Name", FORMAT ( [Date], "dddd" ), "Year-Month", FORMAT([Date], "YYYY-MM") )

What This Code Does

This may look complicated, but it’s quite simple. Let’s break it down:

  • CALENDARAUTO(): This is the hero function. It scans your entire data model for the earliest and latest dates it can find and automatically generates a continuous list of dates between them. This ensures you have no gaps in your calendar.
  • ADDCOLUMNS(): This function does exactly what its name suggests. It takes the single column of dates from CALENDARAUTO() and adds new columns alongside it. We’re adding columns for Year, Quarter, Month, and so on.
  • FORMAT(): We use this to display dates in specific ways. For example, FORMAT([Date], "mmmm") turns a date like "01/15/2024" into the full month name "January."

You now have a robust, independent calendar table. The next crucial step is to connect it to your main data table (e.g., your sales table, web traffic data, etc.).

  1. Go to the Model view (the icon with an ER diagram on the left).
  2. Find your new "Calendar Table" and your main data table (e.g., "Sales").
  3. Click and drag the [Date] column from your "Calendar Table" and drop it on top of the date column in your main data table. A line will appear between them, creating a relationship.
  4. Finally, go back to the Data view, select your "Calendar Table," click on the [Date] column, and under the Column tools tab, click Mark as date table. This tells Power BI to use your new table as the official source of dates for your model.

Step 2: Adding the Dynamic "Rolling Days" Column

Now for the fun part. We will add a calculated column to our beautiful new calendar table. This column will act as our dynamic flag, telling us how many days away each date is from today.

  1. Make sure your "Calendar Table" is selected in the Data view.
  2. Under the Table tools tab, click New column.
  3. Paste the following DAX formula into the formula bar and hit Enter.

Days from Today = DATEDIFF( 'Calendar Table'[Date], TODAY(), DAY )

GraphedGraphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Watch Graphed demo video

Deconstructing the "Rolling" DAX

  • DATEDIFF(date1, date2, interval): This function calculates the difference between two dates. We're telling it to find the difference between the date on each row of our calendar ('Calendar Table'[Date]) and today's date (TODAY()).
  • TODAY(): This simple function retrieves the current date from your system's clock. It updates every time the report is refreshed.
  • DAY: This tells DATEDIFF to return the difference in days.

Now, if you look at your calendar table, you'll see a new column named "Days from Today." Yesterday's date will have a value of 1, the day before will be 2, tomorrow will be -1, and so on. Today's date will be 0. This column is the engine behind our rolling calendar.

Step 3: Creating Your Rolling Time Period Slicer

Let's make this useful. The goal is to create a filter that lets a user easily select "Last 7 Days," "Last 30 Days," or "Last 90 Days." We could just use the Days from Today column, but a slider from 0 to 90 isn't very user-friendly. Instead, let's create another calculated column with clear, descriptive labels.

  1. With the "Calendar Table" still selected, create another New column.
  2. Paste in this DAX formula. We are using a SWITCH statement, which is a cleaner way to write multiple IF statements.

Rolling Period = SWITCH( TRUE(), 'Calendar Table'[Days from Today] >= 0 && 'Calendar Table'[Days from Today] < 7, "Last 7 Days", 'Calendar Table'[Days from Today] >= 0 && 'Calendar Table'[Days from Today] < 30, "Last 30 Days", 'Calendar Table'[Days from Today] >= 0 && 'Calendar Table'[Days from Today] < 90, "Last 90 Days", "Other" // A catch-all for dates outside these ranges )

This formula checks the value in our "Days from Today" column. If it's between 0 and 6, it assigns the label "Last 7 Days." If it's between 0 and 29, it says "Last 30 Days," and so on. Now you have a clean, text-based column perfect for a filter.

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Step 4: Putting It All Together in a Report

You’ve done all the hard work in DAX. Now, using it is easy.

  1. Go back to the Report view (the bar chart icon).
  2. Add a visual to your canvas, like a bar chart showing sales. Make sure the axis uses the [Date] column from your new "Calendar Table", not from your original sales data table.
  3. Now, add a Slicer visual to the report canvas.
  4. Drag the new "Rolling Period" field from your calendar table into the slicer.

That's it! You'll now see a slicer with options for "Last 7 Days," "Last 30 Days," and "Last 90 Days." When you click one, your visuals will automatically filter to show data from that rolling period, and it will update every single day when you refresh the report.

Final Thoughts

Creating a rolling calendar in Power BI is a perfect example of trading a little upfront work for massive long-term benefits. By using DAX to build a dynamic date table, you escape the trap of static reports and move toward automated, always-relevant dashboards that give you a constant pulse on business performance.

Building custom logic like this in Power BI is powerful, but it often involves learning DAX, managing data relationships, and clicking through numerous menus. At Graphed, we've designed an experience to remove this complexity. Instead of writing formulas to define date ranges, you can just ask a question like, "Show me my Shopify revenue over the last 30 days compared to the 30 days before that." We securely connect to your data sources and use natural language to generate live, interactive dashboards for you in seconds, not hours.

Related Articles