Forum Discussion

pgree's avatar
pgree
Regular Visitor
4 years ago
Solved

Investment Return Calculation - User Defined Start and End Dates

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.

DateInvestmentPrice
31/07/2015Basic Materials5417.11
31/08/2015Basic Materials5115.12
30/09/2015Basic Materials4739.60
30/10/2015Basic Materials5346.69
30/11/2015Basic Materials5444.16
31/12/2015Basic Materials5199.55
29/01/2016Basic Materials4683.97
29/02/2016Basic Materials4986.52
31/03/2016Basic Materials5454.90
29/04/2016Basic Materials5731.86
31/05/2016Basic Materials5724.49
30/06/2016Basic Materials5686.19
29/07/2016Basic Materials5999.92
31/08/2016Basic Materials5960.60
30/09/2016Basic Materials5900.81
31/10/2016Basic Materials5772.33
30/11/2016Basic Materials6248.61
30/12/2016Basic Materials6236.75

 

Thank you for any assistance.

DAX Calculation
  • v-cazheng-msft's avatar
    v-cazheng-msft
    4 years ago

    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

1 Reply

  • v-cazheng-msft's avatar
    v-cazheng-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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