Chart & Visualization Issues

How to Create a Power BI Chart for the Previous 13 Months

Bushra Parveen Oct 1, 2026 869 views

Question details

The user needs to display a bar chart showing data for a trailing 13-month period based dynamically on Year and Month slicer selections.

Create a Power BI Chart for the Previous 13 Months Using Slicers

Product: Power BI
Device & OS: not provided
Scenario: Setting up a rolling 13-month visualization that updates based on user-selected slicer values rather than a static relative date.
Observed behavior: Standard relative date filters default to the latest dataset date and fail to automatically adjust to the ending month selected in the slicers.

Before you start

Ensure your dataset includes a properly structured and continuous Date table, and that you have basic familiarity with creating DAX measures.

Solution 1Recommended

Use a Custom DAX Measure and Date Table

Build a DAX measure to calculate a dynamic date range based on slicer inputs and apply it as a visual filter.

To achieve a dynamic 13-month rolling chart, standard relative date filters are insufficient because they do not interact directly with standalone Year and Month slicers. Instead, you must use a custom DAX measure combined with a well-structured Date table.

Construct a dedicated Date table

Ensure you have a Date table in your model containing columns for Year, Month Number, Month Label, and a continuous Date field. Mark this table as the official Date table in Power BI.

Configure the report slicers

Add Year and Month slicers to your report canvas. These will allow end-users to designate the ending month for the 13-month visualization window.

Write the dynamic DAX measure

Create a new measure using DAX that identifies the maximum selected date from your slicers, then calculates whether the dates in your chart fall between that selected month and the preceding 12 months.

Apply the visual-level filter

Select your bar chart, open the Filters pane, and drag your new DAX measure into the 'Filters on this visual' section. Set it to only show data when the measure returns a valid (non-blank) result.

Sort the month labels chronologically

Select the Month Label column in the Data view, navigate to 'Column Tools', and click 'Sort by column'. Choose the Month Number column to ensure the x-axis displays chronologically rather than alphabetically.

Need more advanced DAX help?: For highly specialized modeling or DAX troubleshooting, consider posting your specific model details in the Power BI forums within the Microsoft Fabric Community.

Free Microsoft Office alternative

Analyze and Visualize Data Easily with WPS Office

While Power BI handles complex DAX logic for enterprise datasets, WPS Spreadsheet offers an incredibly accessible way to create charts, pivot tables, and rolling date visualizations without advanced coding. Enjoy a familiar interface and robust charting capabilities absolutely free.

Fully compatible with Microsoft Excel formats (.xlsx, .csv).Built-in robust charting tools for effortless rolling month visualizations.Lightweight software with a familiar, user-friendly interface.Cost-effective alternative for everyday data analysis and reporting.

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't the built-in relative date filter work with my slicer?

Built-in relative date filters in Power BI typically evaluate the timeline based on the current system date or the absolute latest date in the dataset. They are not designed to link dynamically to standalone, user-selected slicer values.

Do I absolutely need a separate Date table for this to work?

Yes. It is a fundamental Power BI best practice to have a dedicated, continuous Date table marked as the 'Date Table' in your model. This ensures DAX time intelligence functions operate correctly across all your visuals.

How do I fix my chart if the months are sorting alphabetically?

In Power BI, text fields like month names sort alphabetically by default. To fix this, select your Month Name column in the Data view, go to 'Column tools' on the ribbon, select 'Sort by column', and choose a numeric column like Month Number.

Excel & Data Problems