Month & Year order

[BoostNest]

Hi would appreciate some help. We are currently limited to usingt he app only not desktop so some of the other solutions don't/can't work for us.

We are trying to create a sales report that show sales by month in order from April through to March as march is the end of our financial year. We can get all the correct months in the financial year but not the order as the report currently either starts at Jan-17 and finishes in Dec-2016.

By changing the sort order, all it seems to do is reverse the order.

Can anyone help or give me some suggestions? The data is automatically fed from a Xero Dataset.

[Anonymous]

You can create a calculated column in the DateTable so the month is in the format YYYYMM

And then you can define "SortByColumn" property for the column that you want to show in your reports so that it would be sorted by this new column.

Hope this helps

Michael

[BoostNest]

Thansk for your reply Michael.

Your suggestion looks like it is using the desktop version to solve the problem. Do you know how to correct this in the online/app version of PBI?

[Anonymous]

So you don't create .pbix desktop file and then Publish it to the online service?

This is the best practice as far as I understand

[Anonymous]

Oh, the data is fed from Xero, I see.

So I would recommend that you use YYYYMM format for Year-Month

This way it will order it ascending . Maybe YYYY-MM will also work for better presentation

Michael

[BoostNest]

Thanks again Michael for your reply, we are running OS X so am trying to get a solution without having to use windows VM.

[Anonymous]

Yes, I understand you don't want to use Desktop.

So I propose that you create a new column in your CalendarTable

=FORMAT(CalendarTable[Date],"YYYYMM") and use this column in your report instead of the current one. 

So you will get the months ordered properly: 201611, 201612, 201701 etc.

All this you can do in the online Service

[BoostNest]

Thanks Michael, looks like an excel formula. I can't see anywhere in the Online app that allows you to enter those, simply edit and apply filters to the Xero dataset, which itself doesn't seem to be able to be edited.

I've had a bit of a google and it appears that you either need to edit in excel with the updates (windows) or use desktop. Is this what you mean?