I’m trying to provide a monthly estimate for ecommerce revenue that is based on the average monthly ecommerce revenue for the past 2 years. However, the formula that I’m working on is giving me TOTAL and not AVERAGE. Can someone please take a look at this and tell me what I’m doing wrong?
Avg Monthly Ecom =
VAR _EcomFilter =
CALCULATE(
[Total Sales],
SalesHeader[Order Source] = “Ecommerce”
)
VAR CurrentYear = CALCULATETABLE(
ENDOFYEAR( Dates[Date] ),
Dates[Date] = TODAY()
)
VAR CurrentMonth = MAX( Dates[MonthOfYear] )
VAR Result = CALCULATE(
AVERAGEX(
SUMMARIZE( Dates, Dates[Year], Dates[MonthOfYear] ),
_EcomFilter
),
REMOVEFILTERS( Dates ),
Dates[YearOffset] > -3,
Dates[MonthOfYear] = CurrentMonth,
Dates[Year] < CurrentYear
)
RETURN
Result
Sales Goals3.pbix (1.8 MB)