topic Re: months with dynamic axis in DAX Commands and Tips

months with dynamic axis

Rai_Lomarques — Fri, 14 Mar 2025 23:33:40 GMT

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

Re: months with dynamic axis

bhanu_gautam — Fri, 14 Mar 2025 12:32:41 GMT

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.

Re: months with dynamic axis

Rai_Lomarques — Fri, 14 Mar 2025 16:34:23 GMT

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.

Re: months with dynamic axis

Anonymous — Mon, 17 Mar 2025 07:30:01 GMT

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.

Re: months with dynamic axis

Anonymous — Thu, 15 May 2025 10:55:02 GMT

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

Re: months with dynamic axis

Anonymous — Fri, 30 May 2025 08:52:48 GMT

Hi @Rai_Lomarques,

May I ask if you have gotten this issue resolved?

Regards,
Vinay Pabbu

Re: months with dynamic axis

Anonymous — Tue, 03 Jun 2025 09:32:13 GMT

Hi @Rai_Lomarques,

May I ask if you have gotten this issue resolved?

Regards,
Vinay Pabbu