Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
@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/434008
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.
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 .
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |