months with dynamic axis

[Rai_Lomarques]

I need to create a physical table in Power BI containing the last 12 months prior to the month that is being filtered in the data segmentation and at the end of this table I need to have an average so that I can use it as the X axis of a graph. How can I achieve this?

My goal is to place the months on the X axis of the graph so that it shows the last 12 months in the format "MM/YYYY" and the average.

See below the expected result:

The example below illustrates that the month '03/2025' is being filtered in the data segmentation, so the table is returning the last 12 months prior to '03/2025'.

I have a table (dCalendar) of dates that is related to other tables

[v-zhengdxu-msft]

Hi   @Rai_Lomarques 

Here I create a set of sample:

Then add a new table:

Calendar =
UNION (
    SUMMARIZE (
        SELECTCOLUMNS (
            'Table',
            "YEAR_MONTH", FORMAT ( 'Table'[Month/Date], "MM/YYYY" ),
            "Date",
                CALCULATE (
                    MIN ( 'Table'[Month/Date] ),
                    FILTER (
                        ALLSELECTED ( 'Table' ),
                        MONTH ( 'Table'[Month/Date] ) = MONTH ( EARLIER ( 'Table'[Month/Date] ) )
                            && YEAR ( 'Table'[Month/Date] ) = YEAR ( EARLIER ( 'Table'[Month/Date] ) )
                    )
                )
        ),
        [YEAR_MONTH],
        [Date]
    ),
    ADDCOLUMNS (
        { "Average" },
        "Date",
            DATE ( YEAR ( MAX ( 'Table'[Month/Date] ) ), MONTH ( MAX ( 'Table'[Month/Date] ) ) + 1, 1 )
    )
)

 

Select the YEAR_MONTH Column and click the Sort by column in the Column tools pane then select [Date]:

Finally add a measure:

MEASURE =
VAR _currentYear =
    YEAR ( SELECTEDVALUE ( 'Calendar'[Date] ) )
VAR _currentMonth =
    MONTH ( SELECTEDVALUE ( 'Calendar'[Date] ) )
RETURN
    IF (
        SELECTEDVALUE ( 'Calendar'[YEAR_MONTH] ) <> "Average",
        CALCULATE (
            SUM ( 'Table'[Values] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                YEAR ( 'Table'[Month/Date] ) = _currentYear
                    && MONTH ( 'Table'[Month/Date] ) = _currentMonth
            )
        ),
        CALCULATE ( AVERAGE ( 'Table'[Values] ), ALLSELECTED ( 'Table' ) )
    )

The result is as follow:

 

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bhanu_gautam]

@Rai_Lomarques 

If you don't already have a date table, you can create one using DAX:

dCalendar =
ADDCOLUMNS (
CALENDAR (DATE(2010, 1, 1), DATE(2030, 12, 31)),
"Year", YEAR([Date]),
"Month", MONTH([Date]),
"MonthYear", FORMAT([Date], "MM/YYYY")
)

Create a measure to calculate the last 12 months based on the selected month:

Last12Months =
VAR SelectedMonth = MAX(dCalendar[Date])
RETURN
CALCULATETABLE (
dCalendar,
DATESINPERIOD (
dCalendar[Date],
SelectedMonth,
-12,
MONTH
)
)

Create a measure to calculate the average of the values you want to display:

AverageValue =
CALCULATE (
AVERAGE(YourTable[YourValueColumn]),
FILTER (
dCalendar,
dCalendar[Date] IN Last12Months
)
)

Add a table visual to your report.
Add the MonthYear column from the dCalendar table to the table visual.
Add the AverageValue measure to the table visual.

To format the X axis to show the months in "MM/YYYY" format, ensure that the MonthYear column is used as the X axis in your graph.

[Rai_Lomarques]

Your solution does not address my issue. I need the "Average" on the X axis and your solution does not have it. In the screenshot example I put the sales in the month columns and the average at the end.