How to calculate rolling average with duplicate dates

[katvaldez]

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! 

[Greg_Deckler]

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

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. 

[katvaldez]

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 .