SUM multiple month values (January) over years

[Anonymous]

I'm trying to sum multiple month values over several years e.g. Jan 2007, Jan 2008, Jan 2009.

I'm using the following formula however when I try push the date forward 12 months it comes back to the orignial value e.g. (Jan 2007) + Jan 2007 instead of (Jan 2007 + Jan 2008)

Is there a way to achieve multiple Jan additions?

PBIX

https://www.dropbox.com/s/giyeda55w6ybv7f/Monthly%20Average%20Over%20Years.pbix?dl=0

I am using Alberto Ferrari's "Rolling Average" file as a sample template

https://www.sqlbi.com/wp-content/uploads/Computing-rolling-average-in-DAX-sample.zip

My DAX is

Aggregation 2 =
CALCULATE (
 SUMX (
    Sales,
    Sales[Quantity] * Sales[Net Price]
 ),
 ALLEXCEPT (
    'Date',
    'Date'[Month]
 ),DATESINPERIOD('Date'[Date],FIRSTDATE('Date'[Date]),1,MONTH))

+

CALCULATE (
 SUMX (
    Sales,
    Sales[Quantity] * Sales[Net Price]
 ),
 ALLEXCEPT (
    'Date',
    'Date'[Month]
 ),DATESINPERIOD('Date'[Date],FIRSTDATE('Date'[Date]),1,MONTH),EDATE('Date'[Date],12))

[amitchandak]

@Anonymous , Based on what I got, try measure one like below with help from date table

last three year same month  Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
+ CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))

+ CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-24,MONTH)))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Anonymous]

Thanks for this,I'm getting this result when I enter the measure, I've tried filtering out the -12 and -24 month and it still gives me the same result

I'm using 'Date' [Calendar Year] and 'Date'[Month] as my rows do you think this is the issue as the measure you've suggested is calling 'Date'[Date]

[v-easonf-msft · Accepted solution]

Hi, @Anonymous 

I think there is no need to use DATESMTD.

NY_Sales Amount = CALCULATE(Sales[Sales Amount],DATEADD('Date'[Date],-1,YEAR) )

Year after NY_Sales Amount = CALCULATE(Sales[Sales Amount],DATEADD('Date'[Date],-2,YEAR) )

_Aggregation = [Sales Amount]+[NY_Sales Amount]+[Year after NY_Sales Amount] 

Please check the attachment for more details. If it doesn't meet your requirement, please feel free to ask.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.