How to ignore blank values while calculating 7 day rolling averages

[Anonymous]

All,

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

@v-yingjl 

@amitchandak 

@MathLacome

[danextian · Accepted solution]

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
)

[amitchandak]

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

[danextian]

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 )

[Greg_Deckler]

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

[VasTg]

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

@VasTg 

@amitchandak 

@danextian 

@Greg_Deckler 

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

[danextian]

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 )

[Anonymous]

@danextian, 

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

[Ashish_Mathur · Accepted solution]

Hi,

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

Hope this helps.

[TatianaHafele]

Thank you, Ashish! It worked great!

[Ashish_Mathur]

You are welcome.

[danextian]

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?

[danextian · Accepted solution]

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
)

[MarvinDexter]

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???

[MarvinDexter]

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