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

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!

 

 

 

1 ACCEPTED SOLUTION

@amitchandak 

 

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!

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

@amitchandak 

 

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!

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!