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] )
)
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 Johnsnowlife,

         

        It is working fine.  I changed the formula a bit and removed the visual level filters.  The file is at the same link.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

    That link does not work.