topic Re: Order a graph by month is not working with parameter in DAX Commands and Tips

Order a graph by month is not working with parameter

mazwro — Wed, 10 Jul 2024 07:14:19 GMT

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?

Re: Order a graph by month is not working with parameter

BeaBF — Tue, 09 Jul 2024 15:41:57 GMT

mazwro Hi! Can you share the pbix file?

Re: Order a graph by month is not working with parameter

mazwro — Wed, 10 Jul 2024 06:49:53 GMT

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

Re: Order a graph by month is not working with parameter

mazwro — Wed, 10 Jul 2024 07:11:29 GMT

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

Re: Order a graph by month is not working with parameter

BeaBF — Wed, 10 Jul 2024 07:13:57 GMT

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

BBF

Re: Order a graph by month is not working with parameter

mazwro — Wed, 10 Jul 2024 07:18:33 GMT

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

Re: Order a graph by month is not working with parameter

BeaBF — Wed, 10 Jul 2024 07:22:47 GMT

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

Re: Order a graph by month is not working with parameter

mazwro — Wed, 10 Jul 2024 07:27:30 GMT

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)

}

Re: Order a graph by month is not working with parameter

BeaBF — Wed, 10 Jul 2024 07:32:24 GMT

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

Re: Order a graph by month is not working with parameter

mazwro — Wed, 10 Jul 2024 08:09:50 GMT

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

Re: Order a graph by month is not working with parameter

BeaBF — Wed, 10 Jul 2024 08:24:41 GMT

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

Re: Order a graph by month is not working with parameter

mazwro — Wed, 10 Jul 2024 08:58:54 GMT

it worked, thank you so much for your help!

Re: Order a graph by month is not working with parameter

BeaBF — Wed, 10 Jul 2024 09:14:08 GMT

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

BBF