exclude first & last period from measure calculation

[mrothschild]

Data example is here: https://www.dropbox.com/sh/440bqruw06y3l7z/AABazv0YaFE8oXlVWAMF8TTJa?dl=0

I tried using the following measure to exclude the first period and last period from my column sum, but the result is incorrect:

_exclude first & last Measure =

VAR ExcludedLastDate = CALCULATE(MAX(AssetReturnTable[Calendar Month]), ALL(AssetReturnTable))

VAR ExcludedFirstDate = CALCULATE(Min(AssetReturnTable[Calendar Month]), ALL(AssetReturnTable))

RETURN

CALCULATE(

SUM(AssetReturnTable[Base]),

FILTER(

AssetReturnTable,

AssetReturnTable[Calendar Month] < DATE(YEAR(ExcludedLastDate), MONTH(ExcludedLastDate),1)),

FILTER(

AssetReturnTable,

AssetReturnTable[Calendar Month] > DATE(YEAR(ExcludedFirstDate), MONTH(ExcludedFirstDate),1))

)

The data is organized as follows, and the desired result is 

1750200

[d_gosbell]

Based on the current logic in your calculation ExcludedFirstDate = 31 Oct 2018 and ExcludedLastDate = 1 Apr 2027

So your sum will include all data greater than 1 Oct 2018 (which includes the ExcludedFirstDate itself ) and less than 1 Apr 2027, so the two filters are not doing much other than excluding any data for 1 Apr 2027.

What logic did you use to arrive at that figure of 1750200.

[mrothschild]

I have a linked table which has "Period Max" by [Asset ID] which was how I was doing it with flattened data in the previously mentioned formula.  But in this spreadsheet, the way I would do it is as follows:

A = [Total Cash Flows by Asset ID] = SUMIFS([Cash Flow],[Asset ID],[@[Asset ID]])

B = [First Period Cash Flows by Asset ID] = SUMIFS([Cash Flow],[Asset ID],[@[Asset ID]],[Period],0)

C = [Last Period Cash Flows by Asset ID] =SUMIFS([Cash Flow],[Asset ID],[@[Asset ID]],[Period],COUNTIFS([Asset ID],[@[Asset ID]])-1)

D = [Desired Output Cash Flows by Asset ID] =SUM([@[Total By Asset ID]],-[@[First Period By Asset ID]],-[@[Last Period by Asset ID]])

So D = A - B - C and outputs 3 unique values of 611,000 + 503,200 + 636,000 = 1,750,200.

E =SUMIFS([Desired Cash Flow by Asset ID],[Period],0)

I have updated the spreadsheet to show the above logic:  https://www.dropbox.com/sh/440bqruw06y3l7z/AABazv0YaFE8oXlVWAMF8TTJa?dl=0