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

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.

    • johnt75's avatar
      johnt75
      Icon for Super User rankSuper User
      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. 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.