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.

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

        Hi johnt75 ,

        Getting total value, but month wise values are blank.