How to sort the rolling 12 months and prior rolling 12 months by month and year

[Jack_Reacher]

Hello, 

I have the following graph, that is showing the current rolling 12 months and prior rolling 12 months using variables (taking Greg's advice, since "Calculate" didn't work for me in this case and it is considered the quantum physics of Power BI), and the issue I have here is that I can't sort the x-axis by month and year (in this format: April 2022 etc., from April through December are months that belong to the year 2022, and January through March belong to the year 2023) 

I've tried all other options with the date table, different DAX calculations, and x-axis formats but the only accurate output occurs when I filter by the [Expanded_Date]Month column. 

Any help is much appreciated. Google Drive, Power BI file used 

AMRolling12M = 

VAR CurrentDate = MAX(AMD_Main[ExpandedDate])

VAR StartDate = DATE(YEAR(CurrentDate) - 1, MONTH(CurrentDate), DAY(CurrentDate))

VAR EndDate = CurrentDate

VAR AMRolling12M =

    CALCULATE(

        SUM(AMD_Main[NullFill]),

        DATESBETWEEN(

            AMD_Main[ExpandedDate],

            StartDate,

            EndDate

        )

    )

RETURN AMRolling12M

AppMan_Prior_R12M = 

VAR CurrentDate = MAX(AMD_Main[ExpandedDate])

VAR PriorRolling12Months =

    SUMX(

        FILTER(

            AMD_Main,

            AMD_Main[ExpandedDate] >= DATE(YEAR(CurrentDate) - 2, MONTH(CurrentDate), DAY(CurrentDate)) &&

            AMD_Main[ExpandedDate] < DATE(YEAR(CurrentDate) - 1, MONTH(CurrentDate), DAY(CurrentDate))

        ),

        AMD_Main[NullFill]

    )

RETURN

    PriorRolling12Months

[v-yanjiang-msft]

Hi @Jack_Reacher ,

The function DATESBETWEEN in the measure can't scan all the table which is affected by context. I modify the formula like this:

AMRolling12M =
VAR CurrentDate =
    MAX ( AMD_Main[ExpandedDate] )
VAR StartDate =
    DATE ( YEAR ( CurrentDate ) - 1, MONTH ( CurrentDate ), DAY ( CurrentDate ) )
VAR EndDate = CurrentDate
VAR AMRolling12M =
    CALCULATE (
        SUM ( AMD_Main[NullFill] ),
        FILTER (
            ALL ( 'AMD_Main' ),
            'AMD_Main'[ExpandedDate] >= StartDate
                && 'AMD_Main'[ExpandedDate] <= EndDate
        )
    )
RETURN
    AMRolling12M

AppMan_Prior_R12M =
VAR CurrentDate =
    MAX ( AMD_Main[ExpandedDate] )
VAR PriorRolling12Months =
    SUMX (
        FILTER (
            ALL ( AMD_Main ),
            AMD_Main[ExpandedDate]
                >= DATE ( YEAR ( CurrentDate ) - 2, MONTH ( CurrentDate ), DAY ( CurrentDate ) )
                && AMD_Main[ExpandedDate]
                    < DATE ( YEAR ( CurrentDate ) - 1, MONTH ( CurrentDate ), DAY ( CurrentDate ) )
        ),
        AMD_Main[NullFill]
    )
RETURN
    PriorRolling12Months

Get the correct result:

I attach the file below for your reference.

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

Best regards,

Community Support Team_yanjiang

[MohammadLoran25]

Hi @Jack_Reacher ,

I do not know if I undersrand right.

But why you dont do like this:

1-On X-Axis, Put the PeriodCal__ Column.

2-Create a CALCULATED COLUMN in AMD_Main Table:

PeriodCalIndex = RANKX(AMD_Main,CONVERT(FORMAT(AMD_Main[ExpandedDate],"YYYYMM"),INTEGER),,ASC,Dense)

3-Sort PeriodCal__ Column by PeriodCalIndex Calculated Column.

It helped?

Mark it as an accepted solution.

Regards,

Loran

[Jack_Reacher]

@MohammadLoran25 , Hello Mohammad, 

Thanks for your reply, unfortunately, it didn't give me the correct output. 
Kindly check the file I have attached here Google Drive, Power BI file used