Spanning Years with Charts

[beardmark]

Hi Everybody,

I've got a really frustrating problem and am going round in circles trying to solve.

I ahve a number reorts set up for my Service Desk (data out of Jira),

An example would be Issues by Priority  as a stacked bar chart with the x axis showing week number and a total of 8 weeks showingusing a slicer.

Post new year the chart went crazy with week 1 showing over on the left and the other 7 weeks on the right with a big gap in between. Makes sense....so I created a new column in the calander table by concaternating the week and year eg 01 - 20. I foolishly thought that this would work but nope, the order became based on number of values (tickets). SO I tried to use the Sort by Column (date) functionality - no good as this just errorred out as the calandar table holds each day and therefore there was a one to many relationship. 

I can't believe that this isn't a standard requirement for rolling over a year break without the chart going haywire....what am I doing wrong 😞  Can't find anything onthe tinternet.

Help appreciated.

Cheers.

[v-lionel-msft]

Hi @beardmark ,

Can you show a sample data model, or a .pbix file if convenient.

Best Regards,
Lionel Chen

[amitchandak]

I am not sure I got it fully.

But if your graph is displaying week like Week 1- 2020, Week 52 -2019 then you can have sort column like

Sort week year - format(data[date],"YYYY") & weeknum(date[date)

But if you are displaying weeks 1, 2, 3, or Like Week 1, Week 2, You need to add the year to get sorting corrected. Because on these numbers it will not take sort column I suggested above.

Can you please explain with an example what is needed. If possible please share a sample pbix file after removing sensitive information.
Thanks.

My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[irobba]

Could you try adding a field to your calendar table, "Weeks from Now" for instance, and use that as the Sort by column on your weeks field perhaps? The value will always be relative to the current date and time so should keep rolling along nicely. 

Something like ...

DATEDIFF([Date], NOW(), WEEK)

Might need to adapt it slightly to take the beginning or end of the week as the reference point if needs be.