Count Months Since Inception with Filters

[Anonymous]

I'm trying to chart the annual return for several investments over a period of time. One of these time periods is "since inception", or since the first point of data for that investment. I also need to be able to filter the chart by investment, and by date to zoom in on a period. I'm trying to create a measure with the number of months since inception.

If I use something like

CALCULATE (
    DISTINCTCOUNT('Returns'[Return Date]),
    FILTER(ALL(Returns),
        Returns[Return Date] <= MAX(Returns[Return Date])
    )
)

then it calculates the number of months since the absolute first investment in any categories, ignoring all filters. If I change the code to use ALLSELECTED, it works until you try to filter by dates, at which point it just shows months since the earliest filtered date.

Is there a way that I can ignore the date filter in the calculations, but take into account the investment filter?

[DAX0110]

Have you tried the ALLEXCEPT filter?

[Anonymous · Accepted solution]

Thank you! Not exactly what I needed, but put me on the right track - I didn't realise you could specify columns in ALL(). Searching for ALLEXCEPT led me to https://msdn.microsoft.com/en-us/library/ee634795.aspx which has a breakdown of when to use which function.

Now I'm using this - I had to add a date dimension table or the MAX comparison didn't work.

CALCULATE (
    DISTINCTCOUNT('Returns'[Return Date]),
    FILTER(ALL('Calendar'[Date]),
        'Calendar'[Date] <= MAX(Returns[Return Date])
    )
)