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
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

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!