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-vpabbu]

Hi @Rai_Lomarques,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,
Vinay Pabbu

[v-vpabbu]

Hi @Rai_Lomarques,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,
Vinay Pabbu

[v-vpabbu]

Hi @Rai_Lomarques,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,
Vinay Pabbu

[Anonymous]

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.