cancel
Showing results for
Did you mean:
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
Super User III

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

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

Proud to be a Super User!

Super User III

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
Post Partisan

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.

Super User III

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

Yes, that is correct.  You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

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

#### Microsoft named a Leader in The Forrester Wave

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

#### 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
Top Kudoed Authors