Forum Discussion

mike_302's avatar
mike_302
Frequent Visitor
8 years ago

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...
  • mike_302's avatar
    mike_302
    Frequent Visitor
    8 years ago
    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's avatar
    mike_302
    Frequent Visitor
    8 years ago

    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.