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] )
)
fhill's avatar
fhill
Resident Rockstar
8 years ago

I"m still not following the interaction between 'BOTH' and how that changes your request.  Can you mock up some new data that clearly shows the overlaps and mock up how you want the data exported based on 'Both' requirements?

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%. 

 

 

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    8 years ago

    Hi Johnsnowlife,

    If my understanding is correct, you want to get the first date when both Funds appear. Please following the steps below and check if it works fine.

    1. Create a measure using the formula 

    Total distinct Funds=CALCULATE( DISTINCTCOUNT(Table[Fund]),ALL(Fund)))


    2. Create a calculated column using the formula.

    Distinct Funds in each date=CALCULATE(CALCULATE(DISTINCTCOUNT(Table[Fund]),ALLEXCEPT(Table,Table[Date]))


    3. Create a measure to get expected result.

    First date =
CALCULATE (
    FIRSTDATE ( Table1[Date] ),
    FILTER (
        Table,
        Table[Distinct Funds in each date] = Table[Total distinct Funds]
    )
)


    Please feel free to ask if you have any questions.

    Best Regards,
    Angelia

     

    • Johnsnowlife's avatar
      Johnsnowlife
      Helper III
      8 years ago

      v-huizhn-msft

       

      I tried your recommendations without success. 

      First Measure

       

      DistinctFunds =
CALCULATE (
    DISTINCTCOUNT ( AllPerfMnthly[Portfolio] ),
    ALL ( AllPerfMnthly[Portfolio] )
)

      Second Measure and also as a column

      DistinctFundsDate =
CALCULATE (
    CALCULATE (
        DISTINCTCOUNT ( AllPerfMnthly[Portfolio] ),
        ALLEXCEPT ( AllPerfMnthly, AllPerfMnthly[Date] )
    )
)

      DistinctFundsDateCol =
      CALCULATE (
       CALCULATE (
       DISTINCTCOUNT ( AllPerfMnthly[Portfolio] ),
       ALLEXCEPT ( AllPerfMnthly, AllPerfMnthly[Date] )
       )
      )

      Expected Result measure

      First date = 
CALCULATE (
    FIRSTDATE ( AllPerfMnthly[Date] ),
    FILTER (
        AllPerfMnthly,
        AllPerfMnthly[DistinctFundsDateCol] = [DistinctFunds]
    )
)

      Table of results

       

       

      First Date doesn't return a result for any periods. 

      And I'd prefer to only use measures without calculated columns if possible. 

       

      • Johnsnowlife's avatar
        Johnsnowlife
        Helper III
        8 years ago

        I've created a measure with a manual entry to demonstrate my desired outcome. 

        For reference, ALSI TRI starts on 31/10/2005 and Equity Fund starts on 30/06/2006. 

         

        My slicer has selected only "ALSI TRI" and "Equity Fund".

         

        MinIntersectDate = 
CALCULATE (
    MIN ( AllPerfMnthly[Date] ),
    INTERSECT (
        FILTER ( ALL ( AllPerfMnthly ), AllPerfMnthly[Portfolio] = "Equity Fund" ),
        ALL ( AllPerfMnthly )
    )
)

        Which gives me MinIntersectDate = 30/06/2006 in this case which is correct. 

         

        Now I need it to give me the correct answer without hard-coding any of the portfolios into the code. And to handle more than 2 portfolios selected.  

  • 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.