cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
katvaldez
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 -

katvaldez_0-1610122531189.png

 

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.

 

katvaldez_1-1610122709911.png

 

katvaldez_2-1610122736224.png

 

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

2 REPLIES 2
Greg_Deckler
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 - 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. 


---------------------------------------

@ 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 https://drive.google.com/file/d/1bXKlcdSEjYQohJBTtUTfsOsZiveohnGC/view . 

Helpful resources

Announcements
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!

secondImage

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.

secondImage

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