Sorting a column based on Financial dates

Power BI Data Model
1

Hi All,

I have trying to sort month name based on Financial month. I tried the work around as per the Sam’s past video below.

[http://portal.enterprisedna.co/courses/305944/lectures/10836953]

But i am getting an error which i have attached here. Also, i have attached the excel date file which i am trying sort in power bi.

Hope someone would put some lights on this.

Thanks in Advance.
Niraj

Dates.xlsx (88.7 KB)

Error
Error1132×575 79 KB

2

@nbaraili,

Try sorting FYMonthName by FYMonthNumber instead. When doing these types of sorts, you need to match granularities, which is why trying to sort FYMonthName by a yearly column will give you an error (each year has 12 month names associated with it) . You need your sort-by column to have a 1:1 non-ambiguous match with your column to be sorted.

  • Brian
3

Hello Brian,

Thank you for the reply.

I tried sorting FYMonthName by FYMonthNumber and still i am getting the error.

Then i keep the data for one Financial year and tried to sort as per the above combination and i got the desired result.

Any thoughts ?

4

@nbaraili,

This should work, but there seems to be something wrong with your date table calculation of FY Month Number. If you look at the table below, whatever formula you are using to generate that column is giving both (some) October and September a value of 3, and sometimes giving January a value of 6 and other times 7. These two columns have the right granularity match, but the error in generating the date table is violating the unambiguous 1:1 match rule for proper sorting.

image

  • Brian
5

Thank you Brian for pointing this out and fix this.

Now it’s working perfectly fine.

6

To the contributors of this post, thank you for all your inputs on this topic we are now tagging it as Solved. To help us learn more about your experience in the forum, please take a moment to answer this short forum survey. We appreciate all your help and suggestions. Thanks!