Investment Return Calculation - User Defined Start and End Dates

[pgree]

Hello, I have a Power BI data table called Investments which has three columns of data; Date, Investment and Price.  The table has time series price data on a dozen different investments.

I would like to be able to choose a start date and an end date using the Date Filter and to also choose one of the dozen investments also using a Filter, and for Power BI to calculate the investments return.

The example data below is for one investment.

Date	Investment	Price
31/07/2015	Basic Materials	5417.11
31/08/2015	Basic Materials	5115.12
30/09/2015	Basic Materials	4739.60
30/10/2015	Basic Materials	5346.69
30/11/2015	Basic Materials	5444.16
31/12/2015	Basic Materials	5199.55
29/01/2016	Basic Materials	4683.97
29/02/2016	Basic Materials	4986.52
31/03/2016	Basic Materials	5454.90
29/04/2016	Basic Materials	5731.86
31/05/2016	Basic Materials	5724.49
30/06/2016	Basic Materials	5686.19
29/07/2016	Basic Materials	5999.92
31/08/2016	Basic Materials	5960.60
30/09/2016	Basic Materials	5900.81
31/10/2016	Basic Materials	5772.33
30/11/2016	Basic Materials	6248.61
30/12/2016	Basic Materials	6236.75

Thank you for any assistance.

[v-cazheng-msft · Accepted solution]

Hi @pgree,

Firstly, you need a Calendar table. You could create with Calculated Table.

calendar = CALENDAR(MIN(Investments[Date]),MAX(Investments[Date]))

Then, you need a Measure.

calculated investment =
VAR startDate =
    MIN ( 'calendar'[Date] )
VAR endDate =
    MAX ( 'calendar'[Date] )
RETURN
    CALCULATE (
        SUM ( Investments[Price] ),
        FILTER (
            Investments,
            Investments[Investment] = SELECTEDVALUE ( Investments[Investment] )
                && Investments[Date] >= startDate
                && Investments[Date] <= endDate
        )
    )

The result looks like this.

Also, attached the pbix file as reference.

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please let me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun