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

Running total: by week number, over multiple years

I want to plot a Running Total of "costs" by week (there are multiple costs entries per week). The plot will run over more than a year. The problem I currently have is PowerBI sums by a "Week of Year" column I have in my dataset -- so Week 1 costs from year 2017 and 2018 are added together.

 

I added the Year Column to my Axis, and changed to Type: Categorical, but for some reason, this results in the LATER year (2018) taking priority over the EARLIER year (2017), so I get 2018 1, 2018 2. How do I switch this around?

 

This is surely not an uncommon use for PowerBI. I've found some similar forum discussions, but nothing that has solved my problem yet. Can someone point me in the right direction?

 

Thanks 

7 REPLIES 7
Highlighted
Regular Visitor

Re: Running total: by week number, over multiple years

All,

 

I simply need a better explanation of the solution in this thread, linked below, as the solution is extremely vague on details. However, it's the solution I need. Can someone expand on the steps of the solution in that link? Please.

 

https://community.powerbi.com/t5/Desktop/How-to-show-just-the-week-number-in-my-chart-when-I-have-da...

Highlighted
Solution Sage
Solution Sage

Re: Running total: by week number, over multiple years

If the problem is just a sort order add another calculated column of the year number as text. Using FORMAT([Year],”YYYY”) and use that on your axis should now be able to sort.

See this for sample worksheet https://community.powerbi.com/t5/Desktop/Sorting-on-a-combo-chart-with-new-release/td-p/483134
Highlighted
Regular Visitor

Re: Running total: by week number, over multiple years

I'll have to give that a shot tomorrow now. Thanks for the response. I will let you know how that goes and follow up. This seems like it should be much easier.
Highlighted
Regular Visitor

Re: Running total: by week number, over multiple years

No luck. I can't get 2 axes -- one with week the other with year. Actually, here is yet another forum, albeit a couple years old, and I suspect this isn't still the best answer: https://community.powerbi.com/t5/Desktop/X-Axis-Sort-by-month-AND-year/td-p/21998

 

Steps I have applied:

1. Load dataset (sensitive dataset, otherwise I'd share).

2. Add Week No. column, refering to Date column. WeekNo. = Date.WeekOfYear([Date])

3. Add Year column, refering to Date column Year = Year([Date])

- Also created a variation of hte year column that is formated as text, and tried dropping that onto the Axis.

 

If I drop the original Date column onto the axis, I can drill through the heirarchy, but lower than Year level, it only shows one Year -- so data from Quarter 1 of multiple years is added together. Same situation if I go to Months (i.e. January 2016, 2017, 2018 data is added together and displayed as January data); and same if I go to days (only 31 data points showing -- so it's adding the data from each respective day of each month, and displaying 31 datapoints.

 

I have in fact tried switching the X-Axis 'Concatenate labels' function on and off. No luck.

 

Please, hoping for something more thorough. Sunday was a day of learning a million ways to not do something -- and to be fair, I learned a lot about PowerBI, but I hope for some support to not go through another million ways.

Highlighted
Super User I
Super User I

Re: Running total: by week number, over multiple years

Hi @mike_302,

 

Can you put the data and the exact output you are looking in google drive / one drive , so that I can try to work out a solution.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Highlighted
Microsoft
Microsoft

Re: Running total: by week number, over multiple years

Hi @mike_302,

 

Please refer to the demo in the attachment. If you still have issues, I have to say something is wrong with your data model. Please share your file.

Running_total

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Microsoft
Microsoft

Re: Running total: by week number, over multiple years

Hi @mike_302,

 

Could you please mark the proper answers as solutions?

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

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

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors