Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Rolling 12 Months Sales

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.

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

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 )
)

View solution in original post

6 REPLIES 6
amitchandak
Super User
Super User

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
Not applicable

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 ?

is -1 month not working as expected.

Anonymous
Not applicable

@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 )
)

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 )
)

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 )
)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.