cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rsbin
Post Partisan
Post Partisan

7 Day Rolling Sum Excluding 0's

Hello @Ashish_Mathur,

I found your code for calculating the rolling sum for 7 days and downloaded the pbix file. 

 

7 days Rolling sum = if(AND(MAX('Calendar'[Date])-[First date available]>=6,[Last date available]-MAX('Calendar'[Date])>=6),
SUMX(TOPN(7,FILTER(DATESBETWEEN('Calendar'[Date],MIN('Calendar'[Date])-15,Min('Calendar'[Date])),[Value]<>0),[Datevalue],DESC),[Value]),BLANK())

 

 I understand the first part of the code is validating there are 7 dates available to calculate.

I am trying to really understand how the rest of the TOPN function is working.  Hoping you could take a few minutes to perhaps comment the different sections.  For example, I am really confused about the "-15".  Really appreciate you insights and this find was a godsend.  Kudos to you for this one!

 

All the Best and I hope you are staying healthy!

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

You are welcome.  MIN('Calendar'[Date])-15 considers the starting date as 15 days prior to the date of the existing row/column in the Table visual.  So the time horizon is from 15 days prior to the date shown in the row till the date shown in the current row.  The additional condition is that the value (a measure) should <>0.  The TOPN function is returning the top 7 days in the time horizon of "15 days prior to the date shown in the row till the date shown in the current row" by the datevalue.  The SUMX() function is then returning the total of the value measure.

Hope this clarifies.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5
amitchandak
Super User
Super User

@rsbin , not very clear  You have try a formaula like

Rolling 7 day = CALCULATE(sum('Table'[Number]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-7,Day))

 

from Today

Rolling 7 day = CALCULATE(sum('Table'[Number]),DATESINPERIOD('Date'[Date],today(),-7,Day))

 

In case you want to ignore nonworking days

 

Need column in date table

Work Day = if(WEEKDAY([Date],2)>=6,0,1)
Work Date = if(WEEKDAY([Date],2)>=6,BLANK(),[Date])
Work Date Cont = if([Work Day]=0,maxx(FILTER('Date',[Date]<EARLIER([Date]) && [Work Day]<> EARLIER([Work Day]) ),[Date]),[Date])
Work Date cont Rank = RANKX(ALL('Date'),[Work Date Cont],,ASC,Dense)

 

This Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Work Date cont Rank]=max('Date'[Work Date cont Rank])))
Last work day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Work Date cont Rank]=max('Date'[Work Date cont Rank])-1))

 

Last 7 work day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Work Date cont Rank]>=max('Date'[Work Date cont Rank])-7 && 'Date'[Work Date cont Rank]<= max('Date'[Work Date cont Rank])))

 

.Traveling Across Workdays - What is next/previous Working day
https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calenda...

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

 

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!
Ashish_Mathur
Super User
Super User

You are welcome.  MIN('Calendar'[Date])-15 considers the starting date as 15 days prior to the date of the existing row/column in the Table visual.  So the time horizon is from 15 days prior to the date shown in the row till the date shown in the current row.  The additional condition is that the value (a measure) should <>0.  The TOPN function is returning the top 7 days in the time horizon of "15 days prior to the date shown in the row till the date shown in the current row" by the datevalue.  The SUMX() function is then returning the total of the value measure.

Hope this clarifies.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Hello @Ashish_Mathur ,   So I could increase the number of days to look at to 30 on the offchance looking back 15 days does not provide sufficient leeway to give me 7 days of valid values.   It took me a while to understand the different components of the TOPN formula. 

 @amitchandak,  Appreciate you kicking in with your response.

   Unfortunately working days / weekends is not applicable in my work environment.  We are a 24/7/365 operation so the concept of weekday / weekend does not exist.   Every individual has a different work schedule.  Hence, when one has 0 activity for a day, it is implied that is their day off.  Thus, I have to exclude that day from their rolling average.  Hence why Ashish's formula works for me.  I will definitely bookmark the link you provided as I'm sure it will come in handy in the future.

 

Thanks again and kudos to you both.

Best Regards and stay healthy.  

 

 

Hi,

I posted my solution at this Blog article as well - Show Balance outstanding everyday even if data for everyday is not available.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Yes, that is correct.  You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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!