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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Aurora-BI
Helper I
Helper I

How to get a rolling average but exclude blank dates

Hi All,

 

I have seen a few posts on here dealing with rolling averages, but none have quite helped me acheive what I am trying to do.

 

I have a table named "Date Group Test" with 2 columns: dates and a value

AuroraBI_0-1712607805374.png

 

but when I try to use: 

20 Day Rolling Average =
VAR NumDays = 20
VAR RollingSum =
CALCULATE(
    SUM('Date Group Test'[Date Count]),
DATESINPERIOD('Date Group Test'[Date Value], LASTDATE('Date Group Test'[Date Value]), -NumDays, DAY)
)
RETURN
RollingSum/NumDays
 
it returns an average number that includes dates that are not in my data set, and gives them a value of 0.
 
My current 20 day rolling average is 55 according to this formula

 

AuroraBI_2-1712607963042.png

 

But when I do the calculation myself the number should be about 86.

 

Furthermore, these are the last 20 days according the the formula:

AuroraBI_0-1712608024534.png

 

but the reality is, this is 13 values and 7 blank values. 

 

Does anyone know how I can filter this data so it does not insert date values I do not have with blank data? 

 

There are no records on dates missing from the table above.

 

Any help is greatly appreciated!

1 ACCEPTED SOLUTION

Hi,

The answer should be 78.  Write these measures

DC = SUM(Data[Date Count])
DV = SUM(Data[Date Value])
Measure = AVERAGEX(TOPN(20,Data,[DV],DESC),[DC])

Ashish_Mathur_0-1712707051476.png

 


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

View solution in original post

7 REPLIES 7
Ashish_Mathur
Super User
Super User

Hi,

Shouldn't the answer be 84.84?  Also, share some dummy data to work with.

Ashish_Mathur_0-1712620298581.png

 


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

@adudani 

@Ashish_Mathur 

How can I share dummy data with you? I do not see anywhere to attatch a Power BI file

You may either simply just paste the data here or share the download link.  Also, please answer the question i asked in my earlier message.


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

the average for the top 20 records is 76, 84.84 might be right for the rows you used but I need the top 20, not 13

 

Date ValueDate Count
4/8/202473
4/5/202425
4/4/202481
4/3/202488
4/2/202492
4/1/2024133
3/28/2024130
3/27/202494
3/26/202490
3/25/202470
3/22/202419
3/21/202497
3/20/202456
3/19/202437
3/12/202475
3/11/202448
3/7/2024104
3/6/202496
3/5/202481
3/4/202471
2/29/202453
2/28/202495
2/27/2024102
2/26/2024104
2/23/202428
2/22/202489
2/21/2024102
2/20/2024100
2/19/202482
2/14/2024105
2/13/2024108
2/12/202485
2/9/202440
2/8/202486
2/7/202492
2/6/202485
2/5/202484
2/2/20241
2/1/202442
1/31/202442
1/30/202487
1/29/202451
1/25/202468
1/24/202444
1/18/202430
1/17/202440
1/16/202438
1/15/202415
1/11/202418
1/10/202445
1/9/20242
1/8/202424
1/5/202424
1/4/202463
1/2/202427
12/26/20236
12/25/20236
12/13/202352
12/12/202334
12/11/202318
11/29/20239
11/28/202323
11/27/202322
11/22/202339
11/21/202320
11/20/20233
11/16/202321
11/15/202312
11/13/20231
7/2/20231
7/1/20231
6/30/20231
6/29/202320
6/28/202313
6/26/20231
6/22/202310
6/13/20238
6/12/20231
5/7/20231
2/23/20231
2/7/20231
1/26/202313
1/25/20232
1/23/20237
1/12/20232

Hi,

The answer should be 78.  Write these measures

DC = SUM(Data[Date Count])
DV = SUM(Data[Date Value])
Measure = AVERAGEX(TOPN(20,Data,[DV],DESC),[DC])

Ashish_Mathur_0-1712707051476.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Aurora-BI
Helper I
Helper I

@adudani 

 

Thank you for the help. 

 

I was able to follow this video guide but am still not gettign the result I need. In my formula, I cannot seem to "find" the column that has the values I need to work against, where as in the video you provided the user was able to access his "[Total Sales]" column without issue.

 

AuroraBI_0-1712611383677.pngAuroraBI_1-1712611411579.png

 

All of the data in my Power BI report comes from a dataverse a table or a company server... I am not sure how I could make a sample file with the data for others to play around without the security being an issue. There is no data that needs to be private, so if you know how I could make a sample copy I will do so.

adudani
Super User
Super User

hi @Aurora-BI ,

 

kindly reference : Calculate Growth From Previous Non Consecutive Date (youtube.com) . 

here instead of growth, you can subsititute the measure.

Since this is over non consecutive days, you may not need to filter. 

 

Regardless, i would use the below to filter if required:

Filter( table, table[columnname] <> blank()  

 

if this doesn't resolve the issue, kindly attach a usable sample input and output masking sensitive information

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.