Forum Discussion

Johnsnowlife's avatar
Johnsnowlife
Helper 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] )
)
Johnsnowlife's avatar
Johnsnowlife
Helper III
8 years ago

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.

 

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

Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

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.

  • Johnsnowlife's avatar
    Johnsnowlife
    Helper III
    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] )
)