Sorting X- axis based on Month-Year slicer selection

[SSRk]

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

[v-pgoloju]

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

[SSRk]

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

[v-pgoloju]

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

[SSRk]

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

[Praful_Potphode]

Hi @SSRk ,

Please try solution suggested by @johnt75 .

I fit doesn't work, let us know.

Thanks and Regards,

Praful

LinkedIn 

[johnt75]

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.

[SSRk]

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

[johnt75]

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

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

[SSRk]

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

Could you please provide possible solutions

[johnt75]

Maybe it needs to use the duplicate date table, try

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

[SSRk]

Hi @johnt75 ,

Getting total value, but month wise values are blank.

[johnt75 · Accepted solution]

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.

[SSRk]

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.

[johnt75]

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.

[SSRk]

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

[v-pgoloju]

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