DAX Average of Months Over Span of Years

[IsaacH]

I am looking to create a dax measure to find the average for each month over the span of many years to extrapulate seasonal trends (ex. finding the average of all January's, February's, March's, etc. from 2014-2022). 

This is my current code:

Monthly Average Industry =

var monthlytotal = SUMX(VALUES('Calendar'[MonthName]),'Sales'[Amount])

var distinctyear = DISTINCTCOUNT('Calendar'[Year])

return

    DIVIDE(monthlytotal,distinctyear)

Any help would be great 🙂

[FreemanZ · Accepted solution]

hi @IsaacH ,

try like:

Monthly Average Industry =

var monthlytotal = SUMX(VALUES('Calendar'[MonthName]), CALCULATE(SUM('Sales'[Amount])))

var distinctyear = DISTINCTCOUNT('Calendar'[Year])

return

    DIVIDE(monthlytotal,distinctyear)