Forum Discussion

Johnsnowlife's avatar
Johnsnowlife
Icon for Helper III rankHelper 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
Icon for Resident Rockstar rankResident Rockstar
8 years ago

I think i'm missing something in the request, is this what you needed?

 

FirstDate = FIRSTDATE(Table1[Date])

 

Johnsnowlife's avatar
Johnsnowlife
Icon for Helper III rankHelper III
8 years ago

Not exactly. If you replace your date "2/2/2017" with "4/1/2017", then you have 2 portfolios for the same date "4/1/2017". But Beta started before Charlie. Now I want to say "bring me the first date, where there is information for BOTH Beta and Charlie". That is, I want the result "4/1/2017"