How to compute next 12 month sales amount starting from 12 month later ?

[Paulyeo11]

Hi All

Today morning i have post a question  on below i never get any reply. :-

https://community.powerbi.com/t5/Desktop/How-to-using-Rolling-expression-to-get-jan-2019-till-dec-20...

May be due to no one undestand my question. 

The reasons i need the expression is because with the expression i can compute sales increase customer base on current 12 month vs 12 month from now.

Paul

[amitchandak]

@Paulyeo11 , File if needed after signature

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

[Paulyeo11]

Hi Amit

Thank you very much , it work fine now.

Paul

[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

Thank you for the expression :-

I try your expression to my application :-

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

above expression got error msg :-

The MAX function only accepts a column reference as an argument.

May i know how to clear the error msg ?

https://www.dropbox.com/s/u89uvu3lvn5mhpp/Rolling%20month%20for%20compute%20sales%20changes_.pbix?dl...

Above is my PBI file :-

Paul