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] )
)
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.  

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      8 years ago

      Hi Johnsnowlife

       

      Try this technique. I worked using your sample data. It seems to work

      First create a New Table from Modelling Tab. This will give us Count of Portfolios against each Date

      Dates&Portfolios =
SUMMARIZE (
    AllPerfMnthly,
    AllPerfMnthly[Date],
    "No_of_Portfolios", COUNT ( AllPerfMnthly[Portfolio] )
)


      Now create a measure in your "AllperfMnthly" table as follows

      CommonDate =
CALCULATE (
    FIRSTDATE ( 'Dates&Portfolios'[Date] ),
    FILTER (
        'Dates&Portfolios',
        'Dates&Portfolios'[No_of_Portfolios]
            = MAX ( 'Dates&Portfolios'[No_of_Portfolios] )
    )
)

       

      Click here to download this file