cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Spanning Years with Charts

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.

3 REPLIES 3
Highlighted
Helper I
Helper I

Re: Spanning Years with Charts

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.

Highlighted
Super User IV
Super User IV

Re: Spanning Years with Charts

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...

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Community Support
Community Support

Re: Spanning Years with Charts

Hi @beardmark ,

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

 

Best Regards,
Lionel Chen

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors