topic Re: Sorting X- axis based on Month-Year slicer selection in DAX Commands and Tips

Sorting X- axis based on Month-Year slicer selection

SSRk — Fri, 27 Feb 2026 10:17:30 GMT

Hi Team,
I am facing a chellenge to sort x-axis based on selection of filter.

Image-1

Here L12M Amount is the last 12 months data from selected month.
PL12M Amount is the previous last 12 months data from selected month.
I want to sort the X- axis month based on selection of month in slicer, if I select Feb 2022, Feb should be at the rightmost position in place of December, remaining months need to be sorted accordingly. So, ideally if we read from right to left Feb to March at the left.

Image 2 is the sorting order I want.

Thank You,
SSRk

Re: Sorting X- axis based on Month-Year slicer selection

johnt75 — Fri, 27 Feb 2026 11:09:20 GMT

You can use the technique described in https://www.sqlbi.com/articles/show-previous-6-months-of-data-from-single-slicer-selection/ to show the previous 12 months from the date in your slicer.

If you want to display just the month name on the axis rather than the year & month, you could create a new column on the Previous Dates table with a date type, the values would be the start of each month, and set the format string to be "mmm". That should give you the behaviour that you're after.

Re: Sorting X- axis based on Month-Year slicer selection

Praful_Potphode — Fri, 27 Feb 2026 12:40:26 GMT

Hi SSRk ,

Please try solution suggested by johnt75 .

I fit doesn't work, let us know.

Thanks and Regards,

Praful

Re: Sorting X- axis based on Month-Year slicer selection

SSRk — Mon, 02 Mar 2026 06:02:55 GMT

Hi johnt75 ,
Thank you for the solution, currently it is working for last 6 months.
But I have to display
last 12 months
previous last 12 months
side by side in clustered column chart based on selection of month year filter.
L12M Amount =

VAR SelectedDate =

CALCULATE (

MAX ( 'Date'[Date] ),

ALLSELECTED ( 'Date' )

)

VAR StartDate =

EOMONTH ( SelectedDate, -12 ) + 1
RETURN

CALCULATE (

sum(Sheet1[consum]),

REMOVEFILTERS ( 'Date' ),

USERELATIONSHIP ( 'Date'[DateKey],'Dim Date Dup'[DateKey] ),

KEEPFILTERS (

'Dim Date Dup'[Date] >= StartDate &&

'Dim Date Dup'[Date] <= SelectedDate))

PL12M Amount =

VAR SelectedDate =

CALCULATE(

MAX('Date'[Date]),

ALLSELECTED('Date')

)

VAR StartDate =

EOMONTH(SelectedDate, -11) + 1

VAR PrevStart =

EDATE(StartDate, -12) -- shift window 12 months back

VAR PrevEnd =

EDATE(SelectedDate, -12)

RETURN

CALCULATE(

SUM(Sheet1[consum]),

USERELATIONSHIP('Dim Date Dup'[DateKey], 'Date'[DateKey]),

FILTER(

ALL('Dim Date Dup'),

'Dim Date Dup'[Date] >= PrevStart &&

'Dim Date Dup'[Date] <= PrevEnd

)

Could you please help me modify the DAX to achieve the requirement?

Thank you,
SSRk

Re: Sorting X- axis based on Month-Year slicer selection

SSRk — Mon, 02 Mar 2026 06:29:35 GMT

VAR SelectedDate =

CALCULATE (

MAX ( 'Date'[Date] ),

ALLSELECTED ( 'Date' )

)

VAR StartDate =

EOMONTH ( SelectedDate, -12 ) + 1
RETURN

CALCULATE (

sum(Sheet1[consum]),

REMOVEFILTERS ( 'Date' ),

USERELATIONSHIP ( 'Date'[DateKey],'Dim Date Dup'[DateKey] ),

KEEPFILTERS (

'Dim Date Dup'[Date] >= StartDate &&

'Dim Date Dup'[Date] <= SelectedDate))

PL12M Amount =

VAR SelectedDate =

CALCULATE(

MAX('Date'[Date]),

ALLSELECTED('Date')

)

VAR StartDate =

EOMONTH(SelectedDate, -11) + 1

VAR PrevStart =

EDATE(StartDate, -12) -- shift window 12 months back

VAR PrevEnd =

EDATE(SelectedDate, -12)

RETURN

CALCULATE(

SUM(Sheet1[consum]),

USERELATIONSHIP('Dim Date Dup'[DateKey], 'Date'[DateKey]),

FILTER(

ALL('Dim Date Dup'),

'Dim Date Dup'[Date] >= PrevStart &&

'Dim Date Dup'[Date] <= PrevEnd

)

Could you please help me modify the DAX to achieve the requirement?

Thank you,
SSRk

Re: Sorting X- axis based on Month-Year slicer selection

johnt75 — Mon, 02 Mar 2026 09:17:05 GMT

L12M Amount looks OK, for the previous last 12 months I think you can use

PL12M Amount = CALCULATE ( [L12M Amount], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

Re: Sorting X- axis based on Month-Year slicer selection

SSRk — Mon, 02 Mar 2026 10:41:31 GMT

I have tried this earlier using sameperiodlastyear(), But I am getting blank.

Could you please provide possible solutions

Re: Sorting X- axis based on Month-Year slicer selection

johnt75 — Mon, 02 Mar 2026 13:40:08 GMT

Maybe it needs to use the duplicate date table, try

PL12M Amount = CALCULATE ( [L12M Amount], SAMEPERIODLASTYEAR ( 'Dim Date Dup'[Date] ) )

Re: Sorting X- axis based on Month-Year slicer selection

SSRk — Tue, 03 Mar 2026 05:18:58 GMT

Hi johnt75 ,

Getting total value, but month wise values are blank.

Re: Sorting X- axis based on Month-Year slicer selection

johnt75 — Tue, 03 Mar 2026 11:56:45 GMT

You can use

PL12M Amount = VAR MaxDate = MAX ( 'Date'[Date] ) VAR StartDate = EOMONTH ( MaxDate, -12 ) + 1 VAR MaxDupeDate = MAX ( 'Dupe Date'[Date] ) VAR Result = IF ( MaxDupeDate >= StartDate && MaxDupeDate <= MaxDate, VAR Result = CALCULATE ( SUM ( Sheet1[consum] ), SAMEPERIODLASTYEAR ( 'Dupe Date'[Date] ), REMOVEFILTERS ( 'Date' ), USERELATIONSHIP ( 'Date'[Date], 'Dupe Date'[Date] ) ) RETURN Result ) RETURN Result

I tried this in a dummy model and it worked.

Re: Sorting X- axis based on Month-Year slicer selection

SSRk — Wed, 04 Mar 2026 07:37:10 GMT

Hi johnt75 ,
Thank you for the solution. It is working fine for the PL12M Amount when both Year and Month are included on the X-axis. However, if the Year is removed, it shows blank.

Is it possible to get the values while keeping only Month on the X-axis? The months also need to sort automatically.

Re: Sorting X- axis based on Month-Year slicer selection

johnt75 — Wed, 04 Mar 2026 08:28:23 GMT

You need the year for context, but what you could maybe do is create a new date type column, set to the start of the month, but set the format string to be "mmm" so it only shows the month.

Re: Sorting X- axis based on Month-Year slicer selection

SSRk — Wed, 04 Mar 2026 09:25:24 GMT

Do you mean that Year needs to be included on the X-axis for the measure to return values? Is that correct?

Re: Sorting X- axis based on Month-Year slicer selection

Anonymous — Fri, 06 Mar 2026 03:08:42 GMT

Hi SSRk,

Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to johnt75 and Praful_Potphode for prompt and helpful responses.

Just following up to see if the Response provided by community members were helpful in addressing the issue. if the issue still persists Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

Re: Sorting X- axis based on Month-Year slicer selection

Anonymous — Tue, 10 Mar 2026 03:22:45 GMT

Hi SSRk,

The measure needs the year information to calculate the correct values. If you use only the month name (Jan to Dec) on the X axis and remove the year, Power BI cannot tell which year the month belongs to. Because of this missing context, time intelligence functions may return blank values. A good solution is to use a date column that includes both year and month, but format it to display only the month name. This way Power BI still has the correct date context for calculations, while the visual shows only the month names.

Thanks & Regards,

Prasanna Kumar

Re: Sorting X- axis based on Month-Year slicer selection

SSRk — Tue, 10 Mar 2026 10:50:04 GMT

Hi All,
Thank you for the help. The solution worked and resolved the requirement.

Re: Sorting X- axis based on Month-Year slicer selection

Anonymous — Wed, 11 Mar 2026 04:48:49 GMT

Hi SSRk,

Great to hear that it's working as expected on your end!. I would suggest accepting your approach as the solution so that it can benefit others as well. It would be really helpful for others in the community who might be facing similar issues and can address them quickly.

Thanks & Regards,

Prasanna Kumar