Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

How to calculate rolling average with duplicate dates

Hi everyone,


I need your help to get the rolling average of my transactions ($) with duplicate dates. 


here is a snippet of my data -



As you may notice, the date column has duplicate values.


these are the formulas i tried

30 Rolling Avg =
var Transactions =
CALCULATE([Sum of Transactions],
DATESINPERIOD('Transaction'[Date].[Date], LASTDATE('Transaction'[Date].[Date]), -30, DAY))
var Dates_in_period =
DISTINCTCOUNT('Transaction'[Date].[Date]), DATESINPERIOD('Transaction'[Date].[Date], LASTDATE('Transaction'[Date].[Date]), -30, DAY))

30 Avg X = AVERAGEX(DATESINPERIOD('Transaction'[Date].[Date], LASTDATE('Transaction'[Date].[Date]),-30, DAY), SUM('Transaction'[Transactions]))


the 30 Rolling Avg is returning blank, while 30 Avg X column is the same as Transactions.






What could be wrong in my formula? What did i missed? Thank you in advance! 

Super User IV
Super User IV

@katvaldez Hard to say, would need to mock it up with sample data if you could post it as text. Probably some wonkiness caused by the TI functions. You may find this helpful -

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.


Might check this out: Rolling Average - Microsoft Power BI Community


Although, I would think that to get a rolling average with duplicate dates you would want to SUMMARIZE or GROUPBY by date first and then calculate your rolling average over that. 


@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Hi Greg, thank you so much for your response. I also tried the quick measure but it is returning blank as well. I would appreciate it, if you can check my data here . 

Helpful resources

PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!


The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.


Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors