How to get a rolling average but exclude blank dates

[Aurora-BI]

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

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

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

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

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!

[Ashish_Mathur]

Hi,

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

[Aurora-BI]

@adudani 

@Ashish_Mathur 

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

[Ashish_Mathur]

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.

[Aurora-BI]

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 Value	Date Count
4/8/2024	73
4/5/2024	25
4/4/2024	81
4/3/2024	88
4/2/2024	92
4/1/2024	133
3/28/2024	130
3/27/2024	94
3/26/2024	90
3/25/2024	70
3/22/2024	19
3/21/2024	97
3/20/2024	56
3/19/2024	37
3/12/2024	75
3/11/2024	48
3/7/2024	104
3/6/2024	96
3/5/2024	81
3/4/2024	71
2/29/2024	53
2/28/2024	95
2/27/2024	102
2/26/2024	104
2/23/2024	28
2/22/2024	89
2/21/2024	102
2/20/2024	100
2/19/2024	82
2/14/2024	105
2/13/2024	108
2/12/2024	85
2/9/2024	40
2/8/2024	86
2/7/2024	92
2/6/2024	85
2/5/2024	84
2/2/2024	1
2/1/2024	42
1/31/2024	42
1/30/2024	87
1/29/2024	51
1/25/2024	68
1/24/2024	44
1/18/2024	30
1/17/2024	40
1/16/2024	38
1/15/2024	15
1/11/2024	18
1/10/2024	45
1/9/2024	2
1/8/2024	24
1/5/2024	24
1/4/2024	63
1/2/2024	27
12/26/2023	6
12/25/2023	6
12/13/2023	52
12/12/2023	34
12/11/2023	18
11/29/2023	9
11/28/2023	23
11/27/2023	22
11/22/2023	39
11/21/2023	20
11/20/2023	3
11/16/2023	21
11/15/2023	12
11/13/2023	1
7/2/2023	1
7/1/2023	1
6/30/2023	1
6/29/2023	20
6/28/2023	13
6/26/2023	1
6/22/2023	10
6/13/2023	8
6/12/2023	1
5/7/2023	1
2/23/2023	1
2/7/2023	1
1/26/2023	13
1/25/2023	2
1/23/2023	7
1/12/2023	2

[Ashish_Mathur · 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])

[Aurora-BI]

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

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]

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