Forum Discussion

SSRk's avatar
SSRk
Frequent Visitor
6 months ago
Solved

Sorting X- axis based on Month-Year slicer selection

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

 

[Sorting Order]
Custom Sort
DAX
Rolling 12 Months
  • johnt75's avatar
    johnt75
    6 months ago

    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.

16 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    6 months ago

    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's avatar
      SSRk
      Frequent Visitor
      6 months ago

      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's avatar
        johnt75
        Icon for Super User rankSuper User
        6 months ago

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

        PL12M Amount =
CALCULATE ( [L12M Amount], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 months ago

    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's avatar
    SSRk
    Frequent Visitor
    6 months ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 months ago

    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's avatar
    SSRk
    Frequent Visitor
    5 months ago

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