cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
rmitchell Frequent Visitor
Frequent Visitor

Rolling 12 month Total - error with leap year?

I'm using the following measure to show LTM Actual Sales but am getting an incorrect figure for February 2017 which I'm thinking must be something to do with 2016 being a leap year? Any ideas how I can work around this?

 

LTM Actual Sales = 
CALCULATE(
	[Actual Sales],
	DATESBETWEEN(
		DateTable[Date],
		NEXTDAY (SAMEPERIODLASTYEAR ( LASTDATE(DateTable[Date] ) ) ),
		LASTDATE(DateTable[Date])
	)
)

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
rmitchell Frequent Visitor
Frequent Visitor

Re: Rolling 12 month Total - error with leap year?

I solved this by swapping the order of the formulas around so I now have:

LTM Actual Sales = 
CALCULATE(
	[Actual Sales],
	DATESBETWEEN(
		DateTable[Date],
		SAMEPERIODLASTYEAR( NEXTDAY  ( LASTDATE(DateTable[Date] ) ) ),
		LASTDATE(DateTable[Date])
	)
)
1 REPLY 1
rmitchell Frequent Visitor
Frequent Visitor

Re: Rolling 12 month Total - error with leap year?

I solved this by swapping the order of the formulas around so I now have:

LTM Actual Sales = 
CALCULATE(
	[Actual Sales],
	DATESBETWEEN(
		DateTable[Date],
		SAMEPERIODLASTYEAR( NEXTDAY  ( LASTDATE(DateTable[Date] ) ) ),
		LASTDATE(DateTable[Date])
	)
)