Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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).
Solved! Go to 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:
Regards,
Xiaoxin Sheng
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:
Regards,
Xiaoxin Sheng
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.
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
I created a same scenario using sample superstore data. PFA...
https://drive.google.com/open?id=1jJJfSBM7Tvm1Du8jsXZR6gRE1bR0MsQS
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:
Regards,
Xiaoxin Sheng
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |