Forum Discussion

Johnsnowlife's avatar
Johnsnowlife
Helper III
8 years ago
Solved

Finding first date shared by multiple products

I am trying to find the first date where my table has information for all selected portfolios.  My table looks like this.     I'm thinking of something like this (written in english): Min (i...
Need Help
  • Johnsnowlife's avatar
    Johnsnowlife
    8 years ago

    Thanks for all the help!

     

    These are the formulas I used to reach my desired result. 

    FirstSharedDate =
CALCULATE (
    MAXX (
        SUMMARIZE (
            ALLSELECTED ( AllPerfMnthly[Portfolio] ),
            [Portfolio],
            "ABCD", MIN ( AllPerfMnthly[Date] )
        ),
        [ABCD]
    ),
    ALLEXCEPT ( AllPerfMnthly, AllPerfMnthly[Portfolio] )
)

InceptionDate =
CALCULATE (
    FIRSTNONBLANK ( AllPerfMnthly[Date], AllPerfMnthly[Date] ),
    ALLEXCEPT ( AllPerfMnthly, AllPerfMnthly[Portfolio] )
)
Johnsnowlife's avatar
Johnsnowlife
Helper III
8 years ago

If my Data table looks like this

DateFundPerformance
31/01/2017ALSI1%
28/02/2017ALSI2%
31/03/2017ALSI3%
31/03/2017Equity Fund4%
30/04/2017ALSI5%
30/04/2017Equity Fund6%
   


I want to compare the return from inception of my "Equity Fund" against the "ALSI". ALSI starts in January but Equity Fund only starts in March. 

In April, I want to show the return since inception for both of them, but only STARTING from March, because that is the first month that both funds existed. I want to start from the latest start date between all the funds I'm comparing. 

 

So ALSI = 8.15% and Equity Fund = 10.24%. 

 

 

Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

Hi Johnsnowlife,

 

I think i have solved the problem.  However, i need tocheck my result.  Please paste a larger data set and also show your expected result for each fund.  In your dataset, please show decimal numbers so that i can compare my result with yours.

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      8 years ago

      Hi Johnsnowlife,

       

      Try these calculated field formulas

       

      Date of investment.  This field is not really required.  It is just for your information.

       

       

      =CALCULATE(MIN(Returns[Date]),ALL('Calendar'))

       

      Date of inititation for return calculation

       

       

      =MAXX(SUMMARIZE(ALL(Returns),[Fund],"ABCD",MIN(Returns[Date])),[ABCD])

       

      Returns

       

      =CALCULATE(SUM(Returns[Performance]),DATESBETWEEN('Calendar'[Date],[Date of initation for return calculation],MAX('Calendar'[Date])))

      You may download the file from here.

       

      • Johnsnowlife's avatar
        Johnsnowlife
        Helper III
        8 years ago

        That hasn't worked for me. 

         

        Try this link for my full dataset with your measures.

         

        If the Portfolios selected are "Equity Fund" and "ALSI" I need to get my begin date to be "2006/06/30".

         

        If I select "Equity Fund" and "ALSI" and "Balanced Fund" I need to get my begin date to be "2013/10/31".

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      8 years ago

      Hi,

       

      That link does not work.