Date Table not sorting

[abihoughton27]

I have created a date table for my benefits report and i have a column for which it will sort in FYmonth order 

this is the relationship between the tables

and when i use the date in a table it 1 doesn't sort in FY month sort and doesn't le me show item with no data?

[v-eqin-msft]

Hi @abihoughton27 ,

Could you please share me with the expected output and your pbix file after removing sensitive data ?

Best Regards,
Eyelyn Qin

[HarishKM · Accepted solution]

@abihoughton27 Hey Mate ,
You can create two Calculated col in your date tb.

Month Year = FORMAT(financials[Date],"Mmm-YY")
Then sort your month year using this month sort col.

Month sort = FORMAT(financials[Date],"yyyymmdd")

this will fulfil your requirement .
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.

Thanks and Regards,
Harish 

[Applicable88]

@abihoughton27 you didn'n sort correctly the monthname to your FYmonthSort. Shouldn't Jan be 1, Feb be2 and so fourth? 

[abihoughton27]

@Applicable88  My financial month starts in may so i want that to be first - so it should be May - Jun 2 etc 

[Applicable88]

@abihoughton27 Whats happens when you use "sort after column"? Does it says that you have many to many relationship?

[abihoughton27]

@Applicable88 where is sort after column? 

i have a many to one relationship 

[Applicable88]

Just click on your monthname column and tell it to "sorft after" the Monthnumbers.

I hope that solves your problem. 

Best. 

[abihoughton27]

I have done that for my FY month sort because i want that to be the order? i am not sure what you mean?

there is no where in the image above which says sort after? 

i want to sort from the fiancial year month sort not from the normal calander month order 

[Applicable88]

The view I got from your screenshot actually. Its not in the PowerBi Report, but in the "Data" screen. That is the middle symbol on the left ribbon in PowerBi. Afterwards just select your table and click on the Monthname column, after wards click the button I showed you in the screenshot and say "sort after" Monthnumber column. Since your Monthnumber field is in the same table it should work.