Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

 

Rolling Averages.JPG

@v-yingjl 

@amitchandak 

@MathLacome

 

2 ACCEPTED SOLUTIONS

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.

View solution in original post

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
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

14 REPLIES 14
amitchandak
Super User
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

danextian
Super User
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.
Greg_Deckler
Super User
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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
VasTg
Memorable Member
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. 

Connect on LinkedIn
Anonymous
Not applicable

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

 

 

DateSumRolling SumAverageSum1Sum2
2/28/2020 3200457.14285732000
2/27/2020 6800971.42857168000
2/26/2020 97001385.7142997000
2/25/2020 134001914.28571134000
2/24/2020 173002471.42857173000
2/23/2020 263003757.14286263000
2/22/20203200263003757.14286263003200
2/21/20203600271003871.42857271003600
2/20/20202900264003771.42857264002900
2/19/20203700261003728.57143261003700
2/18/20203900300004285.71429300003900
2/17/20209000283004042.85714283009000
2/16/2020 220803154.28571220800
2/15/20204000220803154.28571220804000
2/14/20202900204802925.71429204802900
2/13/20202600196002800196002600
2/12/20207600254023628.85714254027600
2/11/20202200252043600.57143252042200
2/10/20202780314094487314092780
2/9/2020 452336461.85714452330
2/8/20202400592378462.42857592372400
2/7/20202020635439077.57143635432020
2/6/20208402615238789615238402
2/5/20207402531217588.71429531217402
2/4/20208405457196531.28571457198405
2/3/202016604373145330.571433731416604
2/2/202014004207102958.571432071014004
2/1/20206706670695867066706

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.
Anonymous
Not applicable

@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

 

DateSumDesired Rolling Sum Desired Rolling Average
2/1/20206706  
2/2/202014004  
2/3/202016604  
2/4/20208405  
2/5/20207402  
2/6/20208402  
2/7/20202020635439077.57
2/8/20202400592378462.43
2/9/2020 592378462.43
2/10/20202780480136859.00
2/11/20202200336094801.29
2/12/20207600328044686.29
2/13/20202600280024000.29
2/14/20202900225003214.29
2/15/20204000244803497.14
2/16/2020 244803497.14
2/17/20209000310804440.00
2/18/20203900322004600.00
2/19/20203700337004814.29
2/20/20202900290004142.86
2/21/20203600300004285.71
2/22/20203200303004328.57
2/23/2020   
2/24/2020   
2/25/2020   
2/26/2020   
2/27/2020   
2/28/2020   

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
https://www.linkedin.com/in/excelenthusiasts/

Thank you, Ashish! It worked great!

You are welcome.


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

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.

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.

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

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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