cancel
Showing results for
Search instead for
Did you mean:
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
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 !!
6 REPLIES 6
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 ?

Super User

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 )
)
Super User
```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 !!
Super User

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 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 Design Challenge

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

Check it out!

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

Top Solution Authors
Top Kudoed Authors