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

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