Forum Discussion
Helper IIIFinding 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 (if Calculate ( Countx(Table, PerfMonthly), filter(table, to show all rows for the selected portfolios in my slicer and the date in the current row ) = DistinctCount(Portfolio)
then return Date for that Row.
else ignore)
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] ) )
17 Replies
- fhill
Resident Rockstar
I think i'm missing something in the request, is this what you needed?
FirstDate = FIRSTDATE(Table1[Date])
JohnsnowlifeNot 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"
- fhill
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?
