cancel
Showing results for
Did you mean:
Member

## 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 (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)

1 ACCEPTED SOLUTION

Accepted Solutions
Member

## Re: Finding 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] )
)```
17 REPLIES 17
Senior Member

## Re: Finding first date shared by multiple products

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

FirstDate = FIRSTDATE(Table1[Date])

Member

## Re: Finding first date shared by multiple products

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"

Senior Member

## Re: Finding first date shared by multiple products

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?

Member

## Re: Finding first date shared by multiple products

If my Data table looks like this

 Date Fund Performance 31/01/2017 ALSI 1% 28/02/2017 ALSI 2% 31/03/2017 ALSI 3% 31/03/2017 Equity Fund 4% 30/04/2017 ALSI 5% 30/04/2017 Equity Fund 6%

I want to compare the return from inception of my "Equity Fund" against the "ALSI". ALSI starts in January but Equity Fund only starts in March.

In April, I want to show the return since inception for both of them, but only STARTING from March, because that is the first month that both funds existed. I want to start from the latest start date between all the funds I'm comparing.

So ALSI = 8.15% and Equity Fund = 10.24%.

Microsoft

## Re: Finding first date shared by multiple products

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

Member

## Re: Finding first date shared by multiple products

@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.

Member

## Re: Finding first date shared by multiple products

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.

Super User III

## Re: Finding first date shared by multiple products

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

Try my new Power BI game Cross the River
Super User IV

## Re: Finding first date shared by multiple products

I think i have solved the problem.  However, i need tocheck my result.  Please paste a larger data set and also show your expected result for each fund.  In your dataset, please show decimal numbers so that i can compare my result with yours.

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

#### Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

#### Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors