Sorting x-axis with same period last year and current month this year for last 13 months.

[Anonymous]

Hello...

I have calculated the count of leads for last 13 months using DAX - 

Leads = CALCULATE(COUNT(dim[lead_sk]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-13,MONTH))

and Same period last year using DAX-

Same period last year = CALCULATE([Leads],SAMEPERIODLASTYEAR('Date'[Date]))

Now I want to diplay these two measures on X-axis using clustered column chart with axis in which months are formatted in this way-
FORMAT(Dim(date),"MMM-YYYY") Aug-2018(Aug-2017) in the brackets is the same period last year. Based on relative date filter we want to display the same for last 13 months.

I tried sorting the axis according to this DAX- YEAR(Dim(date))*100+MONTH(Dim(date)) which gives like 201801,201802 but the challenge we faced here is sorting months and years.

For example, let us consider for 3 months and choose feb 2018.  (Instead of 13 months)

 In the above chart, the years are sorted first and then the months are sorted next.

But we need the axis to be shown and sorted in this order- Dec 2017(Dec2016),Jan2018(Jan 2017),Feb2018(Feb2017).

If we sort according to "months number"(01,02..........12) the axis displays in this order…

Jan-2018(Jan-2017),Feb-2018(Feb-2017),Dec-2017(Dec-2016).

Here Dec-2017(Dec-2016) should come before Jan-2018(Jan-2017),Feb-2018(Feb-2017).

How do we sort it in this order - Dec 2017(Dec2016),Jan2018(Jan 2017),Feb2018(Feb2017).

[v-shex-msft]

Hi @Anonymous,

Maybe you can try to create a sort order table with column which you wanted to customize and index column.

Then you need to create relationship to original table column and use custom sort order column to replace original one to create visualizations.

Reference link:

Custom Sorting in Power BI

Regards,

Xiaoxin Sheng

[Anonymous]

Thanks for the reply @v-shex-msft. But here i am calculating 2 measures based on one column and that is leads.... am taking the count of the leads and calculating according to current and sameperiod last year. 

[v-shex-msft]

Hi @Anonymous,

Can you please provide a pbix file with some sample data that we can test on it? It will try it if custom sort can be enabled on your scenario.

Regards,

Xiaoxin Sheng

[Anonymous]

I created a same scenario using sample superstore data. PFA...

https://drive.google.com/open?id=1jJJfSBM7Tvm1Du8jsXZR6gRE1bR0MsQS

[v-shex-msft · Accepted solution]

HI @Anonymous,

I create a custom sort order table with 'multiple columns ranking' column as index of 'month and year' column, then create relationship to original table and use 'sort order table' columns as axis to achieve your requirement.

Sort Table = 
VAR temp =
    ADDCOLUMNS (
        VALUES ( Orders[Month and Year] ),
        "Year", YEAR ( DATEVALUE ( [Month and Year] ) ),
        "Month", MONTH ( DATEVALUE ( [Month and Year] ) )
    )
RETURN
    SELECTCOLUMNS (
        temp,
        "Month and Year", [Month and Year],
        "Rank", COUNTROWS (
            FILTER (
                temp,
                ISONORAFTER (
                        [Month], EARLIER ( [Month] ), DESC,
                        [Year], EARLIER ( [Year] ), ASC
                )
            )
        )
    )

Snapshots:

Reference link:

Sorting by multiple columns

Regards,

Xiaoxin Sheng