# Rolling Averages In Power BI

Getting a rolling average should be an native function in Power BI. However maybe at the time you read this, there will already be a a feature added. However, in the meantime lets dive into dynamic rolling average using Power BI.

Here are the list of functions will be using the to create our calculation:

• SUM
• CALCULATE
• LASTDATE
• DATESINPERIOD
• DISTINCTCOUNT

There are two points to this formula:

1. Calculating the sum of the value in the period
2. Calculating the count of days in the period
3. Dividing these two calculations

Calculating the Sum during the period

CALCULATE(SUM(VALUE), DATESINPERIOD(DATE,START DATE, NUMBER OF INTERVALS, INTERVAL)

Calculating the count of day in the period

CALCULATE(DISTINCTCOUNT(DATE), DATESINPERIOD(DATE,START DATE, NUMBER OF INTERVALS, INTERVAL)

Formula for a 28 day average

To make this much cleaner you can use variables that will allow achieve the same affect.

Dynamic Power BI Rolling Average with DAX Parameters #### Gaelim Holland

Subscribe
Notify of Inline Feedbacks Ras
1 year ago

Thank you Cathy
9 months ago

Hello i tried this but it is returning blank ☹️. What could be the problem? Thank you in advance for your response! Todd
7 months ago

Thanks! Struggle with this until I ran across your solution. Leny
6 months ago

This is great, but the rolling average takes the proceeding data, is there a way to make it look backwards instead of forwards? MaxB
6 months ago

Hi, very instructive post, yet I am getting the error

A function ‘DATESINPERIOD’ has been used in a True/False expression that is used as a table filter expression. This is not allowed.

how can we avoid this?
{"ft_lang_label":"__label__en","ft_lang_prob":0.8721565,"math_prob":0.8029187,"size":1678,"snap":"2021-43-2021-49","text_gpt3_token_len":391,"char_repetition_ratio":0.111708485,"word_repetition_ratio":0.014285714,"special_character_ratio":0.20143028,"punctuation_ratio":0.101265825,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.9925276,"pos_list":[0,1,2,3,4,5,6,7,8,9,10,11,12,13,14],"im_url_duplicate_count":[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2021-10-17T00:53:04Z\",\"WARC-Record-ID\":\"<urn:uuid:95c93856-830f-4d6a-a874-14731cb2525b>\",\"Content-Length\":\"123357\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:1e751242-7970-4abe-8755-923b63db853d>\",\"WARC-Concurrent-To\":\"<urn:uuid:00192836-388c-484d-8f19-5962a5d32962>\",\"WARC-IP-Address\":\"23.229.182.163\",\"WARC-Target-URI\":\"https://www.absentdata.com/power-bi/rolling-averages-in-power-bi/\",\"WARC-Payload-Digest\":\"sha1:7635NIUHUHLDQW6EHDWJOX2G74WMUGXD\",\"WARC-Block-Digest\":\"sha1:3DZMDCSTB7LPIX5SS7K2B6JPOBGJMVNN\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2021/CC-MAIN-2021-43/CC-MAIN-2021-43_segments_1634323585045.2_warc_CC-MAIN-20211016231019-20211017021019-00329.warc.gz\"}"}