cancel
Showing results for 
Search instead for 
Did you mean: 
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 )
)
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

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 )
)
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!
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.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!
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 )
)
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

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 )
)
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!