Order a graph by month is not working with parameter

[mazwro]

I have a parameter with date aggregation: week / month / quarter

Date Aggregation parameters = {

    ("WEEK", NAMEOF('date table (2)'[WeekStart]), 0),

    ("MONTH", NAMEOF('date table (2)'[MonthYearFormatted]), 1),

    ("QUARTER", NAMEOF('date table (2)'[QuarterFormatted]), 2)

}

MonthYearFormatted = FORMAT('date table (2)'[Date], "yyyy-mm")

WeekStart = CALCULATE(MIN('date table (2)'[Date]), ALLEXCEPT('date table (2)', 'date table (2)'[Week of Year], 'date table (2)'[Year]))

QuarterFormatted = 'date table (2)'[Year] & " / " & 'date table (2)'[Quarter]

This sum up sales for that time period.

But, when I click on month, the order is messed up. I can order it in PBI Desktop, but when I click on week and month again, the order is reset and I get this:, the choice of month makes the order of the graph by turnover and not date. But weeks and quarters order by date

How can I fix it?

[BeaBF]

@mazwro  Hi! Can you share the pbix file?

[mazwro]

but it looks like after changing date paramterer the default sorting is turnover and not date, only when it comes to month

[BeaBF]

@mazwro Have you tried to sort the date field by another column? 

BBF

[mazwro]

When I click on week , the dafault sorting is date:

but when I select month and quarter, it is turnover target:

I can order manually in the graph options, but when I select other aggregation period, it goes back to turnover sort

[BeaBF]

@mazwro Ok, maybe I understood. it is the order with which you created the bookmarks at the top to switch from week to month and quarter. Open the bookmark, sort the chart by month and "update" on the bookmark. Try doing it again with both.

BBF

[mazwro]

Thank you for that, but I am relatively new to this, so could you explain which bookmark you have in mind? The fields where i click which aggregation I want look like this:

Date Aggregation parameters = {

    ("WEEK", NAMEOF('date table (2)'[WeekStart]), 0),

    ("MONTH", NAMEOF('date table (2)'[MonthYearFormatted]), 1),

    ("QUARTER", NAMEOF('date table (2)'[QuarterFormatted]), 2)

}

[BeaBF]

@mazwro No, ok sorry, they are not bookmarks. So what you need to try to do is create a numeric rank column in the 'date table (2)' table for each variable week, month and quarter. then go to the week column and do sort by and choose the numeric sorting column. If you give me a sample of data, I can recreate it for you in a pbix.

BBF

[mazwro]

I made a sample sata, but it messed up my parameters, and the graph does not show the sales per time period, and I am not sure why.

Could not drop a pbix file here, so I am sending via WeTransfer:

https://we.tl/t-9K0qGAWnkQ

[BeaBF · Accepted solution]

@mazwro ok, for each column, you need to do this:

1. Create the column Sort:

SORT_QUARTER_FORMATTED = CONCATENATE(FORMAT('date table (2)'[Year], "#"), 'date table (2)'[QuarterNo])

2. Click on QuarterFormatted column, then "sort by" and choose the calculated column above:

In this way, the graph should maintain this order.

Try and let me know.

BBF

[mazwro]

it worked, thank you so much for your help!

[BeaBF]

@mazwro You're welcome, I'm sorry it took us a while, but it was difficult without pbix. Glad it worked! 🙂

BBF

[mazwro]

unfortunately no it is a very complex report already and i'd need to remove 99% of the data and change struscture to share it