months with dynamic axis

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.

 

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:

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