Forum Discussion

SSRk's avatar
SSRk
Frequent Visitor
8 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
SSRk's avatar
SSRk
Frequent Visitor
8 months ago

Hi johnt75 ,

Getting total value, but month wise values are blank.

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

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