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 IV
Super User IV

@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 !! YouTube Channel !! Connect on Linkedin

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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors