cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## How to ignore blank values while calculating 7 day rolling averages

All,

I am struggling with rolling 7 days averages when some dates have blank values in it. any help will be greatly appreciated.

@MathLacome

2 ACCEPTED SOLUTIONS
Super User

Hi @Anonymous ,

In the approach below, I am assuming that the rolling sum is based on the past 7 nonblank days.

Create a calculated column to index the dates. Those with blank sum will follow the index of the previous nonblank day.

``````Date Index =
VAR DateRank =
RANKX (
DISTINCT (
SELECTCOLUMNS (
FILTER ( 'Table', 'Table'[Sum] <> BLANK () ),
"Date", 'Table'[Date]
)
),
CALCULATE ( MIN ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[Date] ) ),
,
ASC,
DENSE
)
RETURN
IF ( 'Table'[Sum] = BLANK (), DateRank - 1, DateRank )
``````

For the Rolling Sum

``````Rolling Sum =
//get the start date based on Date Index
VAR DateSixNonBlankDaysAgo =
CALCULATE (
MIN ( 'Table'[Date] ),
ALL ( 'Table' ),
'Table'[Date Index]
= EARLIER ( 'Table'[Date Index] ) - 6
)
RETURN
CALCULATE (
SUM ( 'Table'[Sum] ),
FILTER (
ALL ( 'Table' ),
'Table'[Date] >= DateSixNonBlankDaysAgo
&& 'Table'[Date] <= EARLIER ( 'Table'[Date] )
)
)
``````

and the rolling average

``````RollingAverage =
ROUND (
'Table'[Desired Rolling Average] - DIVIDE ( 'Table'[Rolling Sum], 7 ),
2
)
``````

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
Super User

Hi,

You may refer to my solution here.  I have used only measures - no calculated columns.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
14 REPLIES 14
Super User

This formula will give you rolling 7 Days. Are you looking 7 not null days ??

``````Rolling 7 = CALCULATE(sum(table[value]),DATESINPERIOD('Date'[Date],max(Table[Date]),-7,DAY))
Rolling 7 = CALCULATE(sumx(Table,if(isblank(Table[value]),0,Table[value])),DATESINPERIOD('Date'[Date],max(Table[Date]),-7,DAY))  ``````

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @

Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin

Super User

Hello,

Try this calcuated column:

``````Rolling Average =
VAR __SUM =
CALCULATE (
SUM ( 'Table'[Sum] ),
FILTER (
ALL ( 'Table' ),
'Table'[Dates]
>= EARLIER ( 'Table'[Dates] ) - 6
&& 'Table'[Dates] <= EARLIER ( 'Table'[Dates] )
)
)
VAR __COUNT =
CALCULATE (
//ignores cells with blank values, zeroes are included
COUNT ( 'Table'[Sum] ),
FILTER (
ALL ( 'Table' ),
'Table'[Dates]
>= EARLIER ( 'Table'[Dates] ) - 6
&& 'Table'[Dates] <= EARLIER ( 'Table'[Dates] )
)
)
RETURN
DIVIDE ( __SUM, __COUNT )
``````

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
Super User

Do a FILTER('Table',NOT(ISBLANK([Sum])) or use ADDCOLUMNS to add a column to your table where if [Sum] ISBLANK, set to 0, otherwise, [Sum].

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Memorable Member

@Anonymous

Do you want to ignore them in 7 days count when you calculate average?If 2 day of last 7 days is blank, do you want to divide the value by 5 or would you like to go past 7 days and get 8th and 9th?

Also the rolling sum is incorrect for some of the days in the screenshot.

Could you provide the data in excel or table format instead of screenshot? It would be easier for us to work on it.

Anonymous
Not applicable

If 2 days of last 7 days are blank than I would like to include 8th & 9th day in my 7 day rolling average. Its like counting production rate for last 7 working day. Hence I need to consider 8th & 9th day and ingnore non working days such as 5th & 6th. Hope this help.

 Dates = CALENDAR(date(2020,1,1),date(2022,12,31)) Sum = calculate(sum(Phases[Distance in Ft.]),FILTER(all(Phases),Phases[Date]=Dates[Date])) Rolling Sum = Calculate(sum(Dates[Sum]),DATESINPERIOD(Dates[Date],LASTDATE(Dates[Date]),-7,DAY)) Average = divide(Dates[Rolling Sum],7) Sum1 = Calculate(sum(Dates[Sum]),DATESINPERIOD(Dates[Date],LASTDATE(Dates[Date]),-7,DAY)) Sum2 = Calculate(sumx(Dates,if(ISBLANK(Dates[Sum]),0,Dates[Sum])),DATESINPERIOD(Phases[Date],max(Dates[Date]),-7,DAY))

 Date Sum Rolling Sum Average Sum1 Sum2 2/28/2020 3200 457.142857 3200 0 2/27/2020 6800 971.428571 6800 0 2/26/2020 9700 1385.71429 9700 0 2/25/2020 13400 1914.28571 13400 0 2/24/2020 17300 2471.42857 17300 0 2/23/2020 26300 3757.14286 26300 0 2/22/2020 3200 26300 3757.14286 26300 3200 2/21/2020 3600 27100 3871.42857 27100 3600 2/20/2020 2900 26400 3771.42857 26400 2900 2/19/2020 3700 26100 3728.57143 26100 3700 2/18/2020 3900 30000 4285.71429 30000 3900 2/17/2020 9000 28300 4042.85714 28300 9000 2/16/2020 22080 3154.28571 22080 0 2/15/2020 4000 22080 3154.28571 22080 4000 2/14/2020 2900 20480 2925.71429 20480 2900 2/13/2020 2600 19600 2800 19600 2600 2/12/2020 7600 25402 3628.85714 25402 7600 2/11/2020 2200 25204 3600.57143 25204 2200 2/10/2020 2780 31409 4487 31409 2780 2/9/2020 45233 6461.85714 45233 0 2/8/2020 2400 59237 8462.42857 59237 2400 2/7/2020 2020 63543 9077.57143 63543 2020 2/6/2020 8402 61523 8789 61523 8402 2/5/2020 7402 53121 7588.71429 53121 7402 2/4/2020 8405 45719 6531.28571 45719 8405 2/3/2020 16604 37314 5330.57143 37314 16604 2/2/2020 14004 20710 2958.57143 20710 14004 2/1/2020 6706 6706 958 6706 6706
Super User

Hi @Anonymous ,

I am pretty confused with your expected result. Should the sum be divided by 7  or dynamically based on the count of non blank days? For 2/1/2020, I was expecting for  an average of 6706 since there is only one day but your result is showing 958 (6706/7). Anyway, here's my take on the rolling average per your original post plus the additional logic for two blank days.

``````Rolling Average =
VAR CountNoBlanks =
CALCULATE (
COUNTA ( Table_[Sum] ),
ALL ( Table_ ),
DATESINPERIOD ( Table_[Date], Table_[Date], -7, DAY )
)
VAR CountWithBlanks =
//Extend the count range to two more days if CountNoBlanks is <=5
CALCULATE (
COUNTA ( Table_[Sum] ),
ALL ( Table_ ),
DATESINPERIOD ( Table_[Date], Table_[Date], -9, DAY )
)
VAR __COUNT =
IF ( CountNoBlanks <= 5, CountWithBlanks, CountNoBlanks )
VAR AmountNoBlanks =
CALCULATE (
SUM ( Table_[Sum] ),
ALL ( Table_ ),
DATESINPERIOD ( Table_[Date], Table_[Date], -7, DAY )
)
VAR AmountWithBlanks =
//Extend the sum range to two more days if CountNoBlanks is <=5
CALCULATE (
SUM ( Table_[Sum] ),
ALL ( Table_ ),
DATESINPERIOD ( Table_[Date], Table_[Date], -9, DAY )
)
VAR __AMOUNT =
IF ( CountNoBlanks <= 5, AmountWithBlanks, AmountNoBlanks )
RETURN
DIVIDE ( __AMOUNT, __COUNT )
``````

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
Anonymous
Not applicable

Thanks for your response, below are the 2 new columns with desired results. I want to sum only the last 7 workdays when actually production happened. If we get a non working day than the average should exclude it form the 7 day count. I used your functions but the desired results are not coming

 Date Sum Desired Rolling Sum Desired Rolling Average 2/1/2020 6706 2/2/2020 14004 2/3/2020 16604 2/4/2020 8405 2/5/2020 7402 2/6/2020 8402 2/7/2020 2020 63543 9077.57 2/8/2020 2400 59237 8462.43 2/9/2020 59237 8462.43 2/10/2020 2780 48013 6859.00 2/11/2020 2200 33609 4801.29 2/12/2020 7600 32804 4686.29 2/13/2020 2600 28002 4000.29 2/14/2020 2900 22500 3214.29 2/15/2020 4000 24480 3497.14 2/16/2020 24480 3497.14 2/17/2020 9000 31080 4440.00 2/18/2020 3900 32200 4600.00 2/19/2020 3700 33700 4814.29 2/20/2020 2900 29000 4142.86 2/21/2020 3600 30000 4285.71 2/22/2020 3200 30300 4328.57 2/23/2020 2/24/2020 2/25/2020 2/26/2020 2/27/2020 2/28/2020
Super User

Hi,

You may refer to my solution here.  I have used only measures - no calculated columns.

Hope this helps.

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

Thank you, Ashish! It worked great!

Super User

You are welcome.

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

Hi @Anonymous ,

If, for example, in the last 10 days, only 5 days have non-blank values, should the  rolling sum  go back further until the count reaches 7?

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
Super User

Hi @Anonymous ,

In the approach below, I am assuming that the rolling sum is based on the past 7 nonblank days.

Create a calculated column to index the dates. Those with blank sum will follow the index of the previous nonblank day.

``````Date Index =
VAR DateRank =
RANKX (
DISTINCT (
SELECTCOLUMNS (
FILTER ( 'Table', 'Table'[Sum] <> BLANK () ),
"Date", 'Table'[Date]
)
),
CALCULATE ( MIN ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[Date] ) ),
,
ASC,
DENSE
)
RETURN
IF ( 'Table'[Sum] = BLANK (), DateRank - 1, DateRank )
``````

For the Rolling Sum

``````Rolling Sum =
//get the start date based on Date Index
VAR DateSixNonBlankDaysAgo =
CALCULATE (
MIN ( 'Table'[Date] ),
ALL ( 'Table' ),
'Table'[Date Index]
= EARLIER ( 'Table'[Date Index] ) - 6
)
RETURN
CALCULATE (
SUM ( 'Table'[Sum] ),
FILTER (
ALL ( 'Table' ),
'Table'[Date] >= DateSixNonBlankDaysAgo
&& 'Table'[Date] <= EARLIER ( 'Table'[Date] )
)
)
``````

and the rolling average

``````RollingAverage =
ROUND (
'Table'[Desired Rolling Average] - DIVIDE ( 'Table'[Rolling Sum], 7 ),
2
)
``````

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
Regular Visitor

Dear Danextian,

I am a little bit out after I had to pause with PowerBI for 1,5 years.

the Return Formular cannot find the column and is waiting for a measure instead.

What did I do wrong???

Regular Visitor

Forget this, I did a measure instead of a calculated column.
I have another issue. Do you believe it will also work in a scenario where we will have different test series in one table.

So one more column, with different Series names. In my opinion yes, but what do you believe

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors