Forum Discussion
Running Total Without Reset
I have a dataset with the hours worked for multiple projects on each day.
I want a line and column chart that displays the hours worked on a certain project (selected from slicer) with year - week as the shared axis as well as a cumulative line.
I know how to get this to work when using just the date, however, when I create a running total based off the week, it resets each year. How would I prevent this from happening?
The only sort of sulution I have found was creating a YearWeek column, e.g. 201901, 201902 etc. However this is not ideal for displaying the axis.
My current DAX (that resets every year):
Hours running total in Week of Year =
CALCULATE(
SUM('MAMUT'[Hours]),
FILTER(
ALLSELECTED('MAMUT'[Week of Year]),
ISONORAFTER('MAMUT'[Week of Year], MAX('MAMUT'[Week of Year]), DESC)
)
)
Thank you for your time!
Thank you for your reply.
I did manage to get it to work with the YYYYWW format, but found it to be less nice on the UI side of things if I plot it in a bar graph (with the Year and Week of Year combination I can nicely collate them).
But I think I will just have to live with that and go for the YYYYWW solution.
Thank you!
2 Replies
spmvoss ,
You to create a combined measure YYYYWW and use that.
Or you can use Week Rank, Like I have used in file : https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0
CALCULATE( SUM('MAMUT'[Hours]), FILTER( ALLSELECTED('MAMUT'[Week of Year]), ISONORAFTER('MAMUT'[Week Rank], MAX('MAMUT'[Week RANK]), DESC) ) )Better you move Week outside your fact in a new table and create week Rank there.
Thank you for your reply.
I did manage to get it to work with the YYYYWW format, but found it to be less nice on the UI side of things if I plot it in a bar graph (with the Year and Week of Year combination I can nicely collate them).
But I think I will just have to live with that and go for the YYYYWW solution.
Thank you!
