The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
I have a dataset with date that I join to a calendar table, which gives me the date, month, week, etc...
I'm trying to get a 12 month rolling average of sales for the previous 12 months (not counting the current month, so if today is December 12, 2023 I would want the average of the 12 months from December 2022 through November 2023.).
I've tried using Max Date to get the final date to be the end of the previous month (in this example November 30, 2022) but I can't get an average formula to work..
any help is appreciated.
Solved! Go to Solution.
Hello @paguy215,
Can you please try this:
1. Calculate the End of the Previous Month
EndOfPreviousMonth =
EOMONTH(TODAY(), -1)
2. Define the 12-Month Period
StartOfPeriod =
STARTOFMONTH(DATEADD('Date'[Date], -12, MONTH))
3. Create the Rolling 12-Month Average Measure
Rolling12MonthAvg =
CALCULATE(
AVERAGE('Sales'[SalesAmount]),
DATESBETWEEN(
'Date'[Date],
[StartOfPeriod],
[EndOfPreviousMonth]
)
)
Should you require any further assistance, please do not hesitate to reach out to me.
Hello @paguy215,
Can you please try this:
1. Calculate the End of the Previous Month
EndOfPreviousMonth =
EOMONTH(TODAY(), -1)
2. Define the 12-Month Period
StartOfPeriod =
STARTOFMONTH(DATEADD('Date'[Date], -12, MONTH))
3. Create the Rolling 12-Month Average Measure
Rolling12MonthAvg =
CALCULATE(
AVERAGE('Sales'[SalesAmount]),
DATESBETWEEN(
'Date'[Date],
[StartOfPeriod],
[EndOfPreviousMonth]
)
)
Should you require any further assistance, please do not hesitate to reach out to me.
User | Count |
---|---|
16 | |
8 | |
6 | |
6 | |
5 |
User | Count |
---|---|
23 | |
13 | |
13 | |
8 | |
8 |