cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper IV
Helper IV

Accumulative Running Total

Hi Experts,

If anyone could help me to get accumulative running total.

Firstly, i created the following new column measures to count rows of Status Table "Returned" by weekly.

1) Start Month = STARTOFMONTH(Data2020[Batch Return])
2) WeekDay = WEEKDAY([Batch Return],2) //Monday
3) StartofWeek = [Batch Return] -[WeekDay]+1 //monday
4) Month Week = QUOTIENT(DATEDIFF(Minx(FILTER('Data2020',[Start Month]=EARLIER([Start Month])),'Data2020'[StartofWeek]),[Batch Return],DAY),7)+1

 

Running table.JPG

 

Measure to get accumulative running total that i'm using as follow:

CummMeasure = CALCULATE(COUNTROWS('Data2020'),FILTER(ALLSELECTED('Data2020'),'Data2020'[Month Week]<=MAX('Data2020'[Month Week])))

But it's get me to the weekly returned running total instead of Accumulative running total that i'm looking for.
What was wrong?

 

The result that i'm expecting for the accumulative running total like the following:

expected.JPG

 

I'm stuck. Please help.

Thanks

1 ACCEPTED SOLUTION
Community Support
Community Support

Hi @Zaky ,

 

Do you want to calculate the cumulative value for the whole year or recalculate the cumulative value from the beginning of each month?

You can refer the following measures,

 

Accumulative1 = 
CALCULATE([Your measure],FILTER(ALLSELECTED('Date'),'Date'[Date]<=MAX('Date'[Date])))

 

Accumulative2 = 
CALCULATE([Your measure],FILTER(ALLSELECTED('Date'),'Date'[Date]<=MAX('Date'[Date])&&'Date'[Month name]=MAX('Date'[Month name])))

 

accu1.jpg

 

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

 

Best regards,

 

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

BTW, pbix as attached.

View solution in original post

5 REPLIES 5
Community Support
Community Support

Hi @Zaky ,

 

We have checked your pbix file and we have a little confused.

Why the week 2 is 339 in your screenshot, but in your table visual, the week 1 is 339.

Maybe you can refer this measure.

 

Measure = CALCULATE([MeasureReturned],FILTER(ALLSELECTED(Data2020),Data2020[Batch Return]<=MAX(Data2020[Batch Return])))

 

ac1.jpg

 

If you have any question, please kindly ask here and we will try to resolve it.

 

Best regards,

 

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

BTW, pbix as attached.

Community Support
Community Support

Hi @Zaky ,

 

Do you want to calculate the cumulative value for the whole year or recalculate the cumulative value from the beginning of each month?

You can refer the following measures,

 

Accumulative1 = 
CALCULATE([Your measure],FILTER(ALLSELECTED('Date'),'Date'[Date]<=MAX('Date'[Date])))

 

Accumulative2 = 
CALCULATE([Your measure],FILTER(ALLSELECTED('Date'),'Date'[Date]<=MAX('Date'[Date])&&'Date'[Month name]=MAX('Date'[Month name])))

 

accu1.jpg

 

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

 

Best regards,

 

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

BTW, pbix as attached.

View solution in original post

@v-zhenbw-msft 

Finally... it's works as per my expectation! ‌‌😍
In addition, any chances to change the value of column Week which start with numbering/week "1 Week" to Week/numbering "Week 1"?

 

Btw, thanks a lot bro! It was a relief when we managed to complete the challenge.

Thumbs up ‌‌👍

 

Here's the results

 

teaser.JPG

Hi @v-zhenbw-msft,

 

Thank you for your response.

My expected result like the table below where I filter the value data for Returned status starting from July. From there, I want to get accumulative running total.

 

For example: Starting Week 2 of July, total value for status Returned is 339 and so on. While for Accumulative running total for Returned, I want the value to calculate each week like 339 (week 2) + 158 (week 3) = 497 , 497 (week 3) + 102 (week 4) = 599 and so on...

expected1.JPG

BTW, pbix link provided here for you to better understand what is my requirement.

 

 

Thanks.

Super User IV
Super User IV

@Zaky , Try option one of the two


Cumm = CALCULATE(SUM(Data2020[Count of return]),filter(allselected(Data2020),Data2020[Start of week] <=max(Data2020[Start of week])))

or


Cumm = CALCULATE(SUM(Data2020[Count of return]),filter(allselected(Data2020),Data2020[Start of week] <=max(Data2020[Start of week]) &&
Data2020[Status] && max(Data2020[Status]) ))



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!

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

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

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors