Sorting by Month in PowerView

[Magnatolia]

Hi all,

So I'm pretty new to PowerView. I've created a column in the Data Model to format dates into "mmm-yy" format. Created a measure to count the number of times each month occurs using a calculated count.

Problem is it sorts in alphanumeric order and note in date order. I created another measured column with the date as text field which does put my month-year into order but I lose the count-by-month.

Any thoughts? Is this even possible? I've sorted the table in the Data Model by newest to oldest but this doesn't affect the ordering in Powerview.

Thanks heaps!
Maggs

 

[luirib]

So I assume you have a Calendar table. If not, you should. Make sure you have one column that has the month number (=MONTH[Date])

What you need to do, is sort the period which I assume is a column in your calendar table ("mmm-yy" in your example) by the month number I mention above. To achieve this, you need to go to Sort by Column Icon in the PowerPivot screen.

I hope this makes sense to you.

 

[Magnatolia]

So I assume you have a Calendar table. If not, you should. Make sure you have one column that has the month number (=MONTH[Date])

What you need to do, is sort the period which I assume is a column in your calendar table ("mmm-yy" in your example) by the month number I mention above. To achieve this, you need to go to Sort by Column Icon in the PowerPivot screen.

I hope this makes sense to you.

Thanks luirib,

I'm not sure what a calendar table is but I assume I can add a field to the table with that formula. The problem however is this only suits one year. My data covers multiple years.

Thanks!

 

[Magnatolia]

I found workaround. I created a calculated column with the date formatted as "YYYYMM". Thanks again!