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

Forecast 2015	Forecast 2016	Forecast 2017 (Full Year)
6 671	96 302	1 206 760
Desired Result
Measure (Slicer 2015)	Measure (Slicer 2016)	Measure (Slicer 2017)	Incorrectly Calculated for 2017
6 671	102 973	162 461	1 309 733
Calculation	(6 671 + 96 302)	(6 671 + 96 302 + Jan2017+ Feb2017 )	( 6 671 + 96 302 + 1 206 760)

<tbody>
</tbody>

 

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