Rolling 12 Months Sales

[Anonymous]

I am trying to get sum rolling 12 months sales. For example if today is 09/26/2019 then I need to get the sum of sales from 09/27/2018 to 09/26/2019 ( till today). Can some help me with the proper DAX code here.

[amitchandak · Accepted solution]

Way 1

Sales 365 Days = ( 
VAR _Cuur_start = Max('Date'[Date]) -365
VAR _Curr_END = Max('Date'[Date])
return 
calculate(sum(Sales[Sales Amount]),Sales[Sales Date] >= _Cuur_start && Sales[Sales Date] <=  _Curr_END )
)

[Anonymous]

Thanks a lot @amitchandak  with few changes its working fine. 

I am also trying to calculate sales in the similar manner for month. But I believe -30 will not solve this as it will miss the data where months end on 31. Lest say I want to calculate the rolling sum of monthly sales so today it should give me sum of sales from 08/26/2019 to 09/26/2019 and tomorrow 08/27/2019 to 09/27/2019 and so on any idea how to achieve this ?

[amitchandak]

is -1 month not working as expected.

[Anonymous]

@amitchandak This is how I am calculating the last 12 months sales becaue I don't have seperate date table. So here -1 month is not going to work. Max current date is a measure which gives me Date = Today() - 1 

Rolling 365Days Sales = (

var _cuur_start = [Max Current Date] - 365

VAR _Curr_END = [Max Current Date]

return

calculate(sum(MasterData[Actual Sales]),'MasterData'[GL Date] >= _cuur_start && 'MasterData'[GL Date] <= _Curr_END )

)

[amitchandak]

Sales 12 Months = ( 
var _cuur_start_2 = maxx('Date',DATEADD('Date'[Date],-1,MONTH))
VAR _Curr_END = Max('Date'[Date])
return 
calculate(sum(Sales[Sales Amount]),Sales[Sales Date] >= _cuur_start_2 && Sales[Sales Date] <=  _Curr_END )
)

[amitchandak]

Way 2

Sales 12 Months = ( 
var _cuur_start_2 = maxx('Date',DATEADD('Date'[Date],-12,MONTH))
VAR _Curr_END = Max('Date'[Date])
return 
calculate(sum(Sales[Sales Amount]),Sales[Sales Date] >= _cuur_start_2 && Sales[Sales Date] <=  _Curr_END )
)