Forum Discussion
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 =
CALCULATE(
DISTINCTCOUNT('Transaction'[Date].[Date]), DATESINPERIOD('Transaction'[Date].[Date], LASTDATE('Transaction'[Date].[Date]), -30, DAY))
return
Transactions/Dates_in_period
30 Avg X = AVERAGEX(DATESINPERIOD('Transaction'[Date].[Date], LASTDATE('Transaction'[Date].[Date]),-30, DAY), SUM('Transaction'[Transactions]))
Result:
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!
2 Replies
- Greg_Deckler
Community Champion
Anonymous 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 - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000
Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008Might 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.
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 https://drive.google.com/file/d/1bXKlcdSEjYQohJBTtUTfsOsZiveohnGC/view .
