Calculate Prior Last 12 Months Revenue DAX formula

[Anonymous]

Hi,
I have a requirement to display Prior Last 12 Months Revenue which computes the rolling sum of the Sales Amount measure over the prior last 12 months.

 e.g for 12th November 2020, the sum of the sales amount between the dates 12th November 2018 and 12th November 2019

To compute Prior Last 12 Months Revenue, I have tried the below shown DAX formula:

Prior Last 12 Months Revenue =

VAR StartPeriod = DATEADD('Transaction Date'[Date],-1,YEAR)

Return

CALCULATE (

SUM('Transaction'[Sales Amount]),

DATESBETWEEN (

'Transaction Date'[Date],

NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE(StartPeriod ) ) ),

LASTDATE ( StartPeriod )

)

)

The result is as shown below (when analyzed for 1 Client):

The result is coming correctly for 2019 as it is showing Sales Amount values for 2018 i.e. Prior Year. But it also displays 2018 aggregated amounts for 2017 and 2020 Transaction Dates as well which should be left blank ideally.

Please suggest correct DAX to calculate Prior Year Last 12 Months Revenue.
Thanks in advance!

[amitchandak · Accepted solution]

@Anonymous , with help from date table.

Example

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

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

[Anonymous]

Thanks, @amitchandak It worked 👍😊