Running total: by week number, over multiple years

[mike_302]

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 

[v-jiascu-msft]

Hi @mike_302,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[v-jiascu-msft]

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.

Best Regards,

Dale

[CheenuSing]

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

[Seward12533]

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

[mike_302]

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.

[mike_302]

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.

[mike_302]

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