Forum Discussion
12 Month Average
Hi Everyone, I wanted to calculate the average of last 12 month to forecast sales value. I have below table: Here I wanted to refer forecasting average value as actual to calculate next month...
Hi ShaileshKT
First, you will need a separate dates table that is related to your fact in a one-to-many single direction relationship. Then, create either of these measures
Sales L12M Excluding Current Month Avg =
DIVIDE (
CALCULATE (
[Sales],
DATESINPERIOD (
'Date'[Date],
--previous end of month date
EOMONTH ( EDATE ( MAX ( 'Date'[Date] ), -1 ), 0 ),
-12,
MONTH
),
REMOVEFILTERS ( 'Date' )
),
12
)
Average L12M Priot to Current =
CALCULATE (
AVERAGEX (
ADDCOLUMNS (
SUMMARIZE ( 'Date', 'Date'[Calendar Year], 'Date'[Month] ),
"@sales", [Sales]
),
[@sales]
),
DATESINPERIOD (
'Date'[Date],
--previous end of month date
EOMONTH ( EDATE ( MAX ( 'Date'[Date] ), -1 ), 0 ),
-12,
MONTH
),
REMOVEFILTERS ( 'Date' )
)
The second measure takes into consideration that there may not be complete 12 months and so the average is just based on how many months have a value.
Please see attached sample pbix.
Hi danextian,
Thank you for the help. But I am expecting below result.
As per the above image, Average for 2020Aug= (Sales from 2019sep to 2020 Jun+ calculated Average of 2020jul and 2020Aug)/12
Is there any way to use forecasted average values use as actual value to calculate next month average.
Thank you,
Shailesh
What you're trying to achieve wihch DAX isn't.