Sort by Year-Month

DAX DAX Calculations
1

Hi,

I’ve attached the simplest of data source and pbix files… one table, two columns. I’ve reviewed “how to” videos for about two hours with what I think could/should work, but I’m really looking for the best practice approach; or at least the one which results in a clearly understandable visualization. A line chart visualization is used.

  1. The goal is to show both the count and the % of total for the same line; presumably as data labels. As you can see, I’ve have separate count and % columns in the ‘Values’ and ‘Secondary values’ elements of the line graph where a second line is produced and not easily formatted for display of data labels. A '[combo] line and clustered column chart may be a visual improvement, but I’m looking for what you think is the best way to accomplish this with a single line graph. Or a better visualization representation of the monthly trend of both count and %? Of note, re-sizing the visual has a large impact on how the data labels display.

  2. Data [per the End Date column] has been captured from November 2020 to date. While the chart visual sorts “correctly” from a month number perspective, it does not sort correctly based on Year-Month where 2020-November and 2020-December would be left-most data points in the graph.
    So, is there a Power BI / DAX solution to sort the line graph points by Year-Month without using a more robust date table with a Year-Mo column and joining the End Date column to it? If yes, please describe and modify the pbix. If not, please provide or link me to your recommended “Calendar date” table for doing this… and hopefully modify the pbix to utilize it.

Everything is fair game… a better visualization, the easiest way, the “best” way.

Many thanks for sharing recommended approaches here,
Kevin

Sample PBI.pbix (107.3 KB)
Sample Data.xlsx (131.2 KB)

2

@kkieger ,

Wow, is this timely! I just completed a video two nights ago, scheduled to run next Monday about pitfalls involving time intelligence fields like Month & Year, and how to solve them. Given that you are doing time intelligence, you absolutely need to have a dedicated date table in your data model, and I very strongly recommend using @Melissa’s outstanding Extended Date Table. In that table, there is a field called Month & Year to which you can apply another field in the table called Monthnyear as a numeric sort column.

This video goes through the basics of generating the table from the M code on the Forum.

In terms of visualization, recently Power BI added the dual scale option to the line chart, which I think should work well for your purposes of representing both count and percentage.

I hope this is helpful.

– Brian

3

Thanks, Brian. I’m not sure how the dual scale feature solves the preferred result of one line displaying both the count and % values as data points. Something similar to the data label display options of a pie or donut chart where both “show value as” options are available.

Please advise on how you would change my line graph visual.

Kevin

4

Hello all. Bumping this post for more visibility.

5

Hi @kkieger,

I’m assuming you resolved your 2nd query by incorporating a Date table
As for the first see if this meets your requirement

image
image950×509 33.3 KB

I’ve set the starting value for both the primary and secondary Y axsis to 0, that makes them overlap each other. Next set the Data and Data label colors and disabled the secondary axsis.

I hope this is helpful.

2 Likes
6

Perfect. Thank you Melissa. Issue resolved.