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...
[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.

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

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


        Could you please provide possible solutions