cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Paulyeo11
Impactful Individual
Impactful Individual

How to using Rolling expression to get jan 2019 till dec 2019 sales amount ?

Hi All

 

Below is my expression working fine , it return 9550 amount  , which is last 12 month sales from today date , in case the today 2021 Jan it will capture next 12 month sales amount .

 

Rolling 12 = CALCULATE(sum(SALES[sales]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH))

 

Paulyeo11_0-1608677984147.png

 

Now i need expression to get the next 12 month sales from Dec 2019 - Jan 2019 :-

 
 

Paulyeo11_0-1608678292611.png

 

https://www.dropbox.com/s/7vs0iks8pswbqjy/Rolling%20month%20for%20compute%20sales%20changes.pbix?dl=...

 

Above link is my PBI file 

 

Paul

 

1 ACCEPTED SOLUTION
amitchandak
Super User IV
Super User IV

@Paulyeo11 , Try like

 

Last 12 before 12  = CALCULATE(SUM(Sales[Net Sales]), DATESINPERIOD('Date'[Date], maxx('Date', DATEADD('Date'[Date],-12,MONTH)),-12, MONTH))


Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

4 REPLIES 4
Icey
Community Support
Community Support

Hi @Paulyeo11 ,

 

Please let us know if the replies above are helpful.

 

If they are, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

 

If not, please give us more details.

 

 

Best Regards,

Icey

amitchandak
Super User IV
Super User IV

@Paulyeo11 , Try like

 

Last 12 before 12  = CALCULATE(SUM(Sales[Net Sales]), DATESINPERIOD('Date'[Date], maxx('Date', DATEADD('Date'[Date],-12,MONTH)),-12, MONTH))


Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

amitchandak
Super User IV
Super User IV

@Paulyeo11 , I am assuming you need 12 before 12.

So these are last 12

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

 

These are 12 before 12

 

Rolling 12 till last 12 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-12,month)),-12,MONTH))
Rolling 12 till last 12 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(dateadd(Sales[Sales Date],-12,month)),-12,MONTH))
Rolling 12 till last 12 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(dateadd('Date'[Date],-12,month)),-12,MONTH))

 

 

2nd last year

2nd Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
2nd Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-2,Year)),"12/31"))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Paulyeo11
Impactful Individual
Impactful Individual

Hi Amit

 

You provide me the beow expression is what i need , it does not have any error , but it display null

_Roll next12 = CALCULATE(sum(SALES[sales]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(SALES[date],-12,month)),-12,MONTH))

Paulyeo11_0-1608694507641.png

 

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors