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 need the year for context, but what you could maybe do is create a new date type column, set to the start of the month, but set the format string to be "mmm" so it only shows the month.

SSRk's avatar
SSRk
Frequent Visitor
6 months ago

Do you mean that Year needs to be included on the X-axis for the measure to return values? Is that correct?

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 months ago

    Hi SSRk,

     

    The measure needs the year information to calculate the correct values. If you use only the month name (Jan to Dec) on the X axis and remove the year, Power BI cannot tell which year the month belongs to. Because of this missing context, time intelligence functions may return blank values. A good solution is to use a date column that includes both year and month, but format it to display only the month name. This way Power BI still has the correct date context for calculations, while the visual shows only the month names.

     

    Thanks & Regards,

    Prasanna Kumar