Sort Month Year for large amount of data

[jesly_ajin]

Dear All,

I have a table with transaction date and groups, i need bar chart month wise (different years), but i am unable to sort the month year. I tried all the ways mentioned on web. but nothing works. Kindly help.

Thanks,

Jesly Thomas

[parry2k · Accepted solution]

@jesly_ajin I believe you have date dimension in your model. Add following two column in it.

Month = FORMAT( YourDateTable[Date], "MMM, yy" )

Month Sort = FORMAT( YourDateTable[Date], "YYYY-MM" )

Select Month column, go to modelling tab, on drop down by Sort By Column choose, Month Sort

In your visual, use Month column from date dimension and everything will work.

[jesly_ajin]

Hi @parry2k 

I have used the below formulas. But still i get the above mentioned error.

Month = FORMAT(LTEQUIPMENTALLOCATIONHISTORY[TRANSDATE], "MMM, yy" )

Month Sort = FORMAT( LTEQUIPMENTALLOCATIONHISTORY[TRANSDATE], "YYYY-MM" )

[jesly_ajin]

Hi @parry2k ,

Now the error went, i changed one of ur formula to Month = FORMAT(LTEQUIPMENTALLOCATIONHISTORY[TRANSDATE], "MMM, yyyy" ). But, still the sort is not working.

NOw my formulas are: 

Month = FORMAT(LTEQUIPMENTALLOCATIONHISTORY[TRANSDATE], "MMM, yyyy" )

Month Sort = FORMAT( LTEQUIPMENTALLOCATIONHISTORY[TRANSDATE], "YYYY-MM" )

Please help. eagerly waiting for help.

[Cmcmahan]

When you say the sort is not working, do you mean that you're still getting the same error when using the Sort By Another Column feature, or that the order is wrong in the visual?  What (incorrect) order are they in now?  I ask because this looks like it should be working. 

If you're still getting the error message that tells you that you can't sort because there's more than one value, double check for nulls or blanks in your data. 

You can also create a measure to test the uniqueness of each "MMM, yyyy"/"yyyy-MM" pairing. If you put this measure into a card, it should display zero.  If not, it should be a count of [Months] that have multiple [Month Sort]s, which is preventing your Sort By Another Column.

TestForUniquePairs = COUNTROWS(FILTER(SUMMARIZE( VALUES(LTEEQUIPMENTALLOCATIONHISTORY[Month], "Pairs", DISTINCTCOUNT(LTEEQUIPMENTALLOCATIONHISTORY[Month Sort])), [Pairs]>1))

[jesly_ajin]

@Cmcmahan,

The error - Sort by another is gone.

But, still the order is incorrect.

I tried the measure formula - it is giving the result as one on the card.

Kindly help.

[Cmcmahan]

Huh.  The fact that it's giving you a result of one is weird, but if the Sort By Another Column is working, we can ignore that.

What order is it currently being displayed in? Can you take a picture of the visual?

[parry2k]

@jesly_ajin at this point I would highly recommend to share pbix file with sample data and remove any sensitive information before sharing. It is pretty straight forward task and should work. Can you share file?

[jesly_ajin]

Please help me with a suitable solution.

[jesly_ajin]

@parry2k 

I tried your method, but i am getting the below error. kindly help.

[parry2k]

@jesly_ajin can you share expression of new columns you added