Forum Discussion
SUM multiple month values (January) over years
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.
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.
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]
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.