7 Day Rolling Sum Excluding 0's

[rsbin]

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!

[amitchandak]

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

[Ashish_Mathur · Accepted solution]

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.

[rsbin]

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.  

[Ashish_Mathur]

Hi,

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

[Ashish_Mathur]

Yes, that is correct.  You are welcome.