Forum Discussion
Helper IIIFinding first date shared by multiple products
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] ) )
Helper IIII'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.
Community ChampionHi 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] )
)
)