Time Intelligence- Cumulative Total

[Sonova]

Hi

I am trying to create a time intelligent function that will calculate the cumulative Forecast total based on a year time filter/slicer selection.

In addition to this the cumulative total should cut off at the last actual transaction for the current year which is Feb 2017.

My formulas work for the 2015 and 2016 year but when I select the 2017 year it will calculate the total forecast up until December 2017.

My Formulae to do this are below, to calculate the last actual and the cum total, I am not sure how to condition the cum total formula to cut off for the 2017 period.

Last Actual:=CALCULATE( LASTDATE( MonthlyData[StartOfMonth] ) , FILTER( ALL( MonthlyData ), NOT( ISBLANK( MonthlyData[Actual] ) ) ) , ALL( Categories ) )

CumTotal=IF( FIRSTDATE( 'Calendar'[Date] ) <= [Last Actual] ,calculate(sum([Forecast]),filter(ALL('Calendar'[Date]) ,'Calendar'[Date]<=max('Calendar'[Date]))))

[TABLE="width: 500"]
<tbody>[TR]
[TD]Forecast 2015[/TD]
[TD]Forecast 2016[/TD]
[TD]Forecast 2017 (Full Year)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6 671[/TD]
[TD]96 302[/TD]
[TD]1 206 760[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Desired Result[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Measure (Slicer 2015)[/TD]
[TD]Measure (Slicer 2016)[/TD]
[TD]Measure (Slicer 2017)[/TD]
[TD]Incorrectly Calculated for 2017[/TD]
[/TR]
[TR]
[TD]6 671[/TD]
[TD]102 973[/TD]
[TD]162 461[/TD]
[TD]1 309 733[/TD]
[/TR]
[TR]
[TD]Calculation[/TD]
[TD](6 671 + 96 302)[/TD]
[TD](6 671 + 96 302 + Jan2017+ Feb2017 )[/TD]
[TD]( 6 671 + 96 302 + 1 206 760)[/TD]
[/TR]
</tbody>[/TABLE]

 

[Sonova]

I have found the solution to my issue.

=CALCULATE( 	[Sum Forecast] , 
	FILTER( 
	ALL( 'Calendar'[Date] ) , 
	'Calendar'[Date] 
		<= if(
			'Calendar'[Date]<=[Last Actual], 
			MAX( MonthlyData[StartOfMonth] ) , 
			[Last Actual]
		)
	)
)