Lookup a value as of a date within a filtered range of dates

[petrosk]

Suppose I have a Fact Table of Account Balances, Investments, and Dates (reference below illustration). This Fact Table is related to a Calendar Dimension Table. How could I determine the account balance for a specific investment within a filtered date range?

Examples: (1) Show me the Account Balance of Investment 101 as of the Maximum Date in Calendar Year 2015. (2) Show me the Account Balance of Investment 103 as of the Minimum Date in Calendar Year 2016.

I imagine there is a simple solution to this issue, but I have been struggling to find an answer.

Fact Table Illustration

Date	Balance	InvestmentID
Friday, December 30, 2016	5.00	101
Friday, December 30, 2016	7.00	102
Friday, December 30, 2016	10.00	103
Wednesday, November 30, 2016	10.00	101
Wednesday, November 30, 2016	8.00	102
Wednesday, November 30, 2016	8.00	103
Monday, October 31, 2016	14.00	101
Monday, October 31, 2016	12.00	102
Monday, October 31, 2016	8.00	103
Friday, September 30, 2016	12.00	101
Friday, September 30, 2016	10.00	102
Friday, September 30, 2016	15.00	103
Wednesday, August 31, 2016	3.00	101
Wednesday, August 31, 2016	10.00	102
Wednesday, August 31, 2016	12.00	103
Friday, July 29, 2016	1.00	101
Friday, July 29, 2016	8.00	102
Friday, July 29, 2016	7.00	103
Thursday, June 30, 2016	3.00	101
Thursday, June 30, 2016	11.00	102
Thursday, June 30, 2016	8.00	103
Tuesday, May 31, 2016	13.00	101
Tuesday, May 31, 2016	4.00	102
Tuesday, May 31, 2016	12.00	103
Friday, April 29, 2016	1.00	101
Friday, April 29, 2016	2.00	102
Friday, April 29, 2016	14.00	103
Thursday, March 31, 2016	15.00	101
Thursday, March 31, 2016	6.00	102
Thursday, March 31, 2016	14.00	103
Monday, February 29, 2016	9.00	101
Monday, February 29, 2016	6.00	102
Monday, February 29, 2016	5.00	103
Friday, January 29, 2016	13.00	101
Friday, January 29, 2016	15.00	102
Friday, January 29, 2016	1.00	103
Thursday, December 31, 2015	3.00	101
Thursday, December 31, 2015	13.00	102
Thursday, December 31, 2015	14.00	103
Monday, November 30, 2015	3.00	101
Monday, November 30, 2015	15.00	102
Monday, November 30, 2015	14.00	103
Friday, October 30, 2015	1.00	101
Friday, October 30, 2015	10.00	102
Friday, October 30, 2015	11.00	103
Wednesday, September 30, 2015	5.00	101
Wednesday, September 30, 2015	10.00	102
Wednesday, September 30, 2015	10.00	103
Monday, August 31, 2015	11.00	101
Monday, August 31, 2015	3.00	102
Monday, August 31, 2015	9.00	103
Friday, July 31, 2015	2.00	101
Friday, July 31, 2015	6.00	102
Friday, July 31, 2015	4.00	103
Tuesday, June 30, 2015	2.00	101
Tuesday, June 30, 2015	1.00	102
Tuesday, June 30, 2015	10.00	103
Friday, May 29, 2015	15.00	101
Friday, May 29, 2015	14.00	102
Friday, May 29, 2015	3.00	103
Thursday, April 30, 2015	8.00	101
Thursday, April 30, 2015	2.00	102
Thursday, April 30, 2015	9.00	103
Tuesday, March 31, 2015	5.00	101
Tuesday, March 31, 2015	10.00	102
Tuesday, March 31, 2015	9.00	103
Friday, February 27, 2015	6.00	101
Friday, February 27, 2015	8.00	102
Friday, February 27, 2015	4.00	103
Friday, January 30, 2015	6.00	101
Friday, January 30, 2015	10.00	102
Friday, January 30, 2015	2.00	103
Wednesday, December 31, 2014	9.00	101
Wednesday, December 31, 2014	14.00	102
Wednesday, December 31, 2014	5.00	103

[MattAllington · Accepted solution]

You need a semi additive measure pattern. Read up in it here http://www.sqlbi.com/articles/semi-additive-measures-in-dax/