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

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

        Maybe it needs to use the duplicate date table, try

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