cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Re: Finding first date shared by multiple products

Hi Ashish

 

Here is my full data set

Super User IV
Super User IV

Re: Finding first date shared by multiple products

Hi,

 

That link does not work.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Super User IV
Super User IV

Re: Finding first date shared by multiple products

Hi @Johnsnowlife,

 

Try these calculated field formulas

 

Date of investment.  This field is not really required.  It is just for your information.

 

 

=CALCULATE(MIN(Returns[Date]),ALL('Calendar'))

 

Date of inititation for return calculation

 

 

=MAXX(SUMMARIZE(ALL(Returns),[Fund],"ABCD",MIN(Returns[Date])),[ABCD])

 

Returns

 

=CALCULATE(SUM(Returns[Performance]),DATESBETWEEN('Calendar'[Date],[Date of initation for return calculation],MAX('Calendar'[Date])))

You may download the file from here.

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Re: Finding first date shared by multiple products

That hasn't worked for me. 

 

Try this link for my full dataset with your measures.

 

If the Portfolios selected are "Equity Fund" and "ALSI" I need to get my begin date to be "2006/06/30".

 

If I select "Equity Fund" and "ALSI" and "Balanced Fund" I need to get my begin date to be "2013/10/31".

Super User IV
Super User IV

Re: Finding first date shared by multiple products

Hi @Johnsnowlife,

 

It is working fine.  I changed the formula a bit and removed the visual level filters.  The file is at the same link.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Re: Finding first date shared by multiple products

I see that it is working by itself. Please look at my latest PBIX file with your measures and my full data. You'll see if created a measure using GroupBy that gets to the same answer too. 

 

If I use your "First Initiation Date" measure in a table by itself (bottom table) I get the correct answer with both our formulas.

 Performance Tavles.PNG

But the objective of the formula is to use it in a table (top table) to get the correct start date for the performance figures for the specified month.

 

Currently, the StartDate formulas don't work if I've specified the latest month in the visual slicer. Or the performance measures don't work if I haven't specified the month on the visual slicer. 

 

I'm not sure which one should be changed.  

 

Thank you for the help so far. I hope this challenge is interesting to you...

Super User IV
Super User IV

Re: Finding first date shared by multiple products

Hi,

 

Why should there be 2 dates selection routes - you have dragged dates both to the filter section and are also using it as a slicer.  Keep any one of them.  Select a date/date range there and then see the result of my formula.  If you still face problems, please let me know exactly with which selection do you face a problem.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

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!

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?

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

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

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors