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

[Paulyeo11]

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

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

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

Above link is my PBI file 

Paul

[Icey]

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 · Accepted solution]

@Paulyeo11 , Try like

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

[amitchandak]

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

[Paulyeo11]

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