Last Date after a Slicer Selected Date

[one3nine0]

I have a table that has a list of publications, their publishing dates, and their end dates. The end dates being the last date before the publication republishes a new version (but the name of the publication stays the same). Table is named Tracker

I also have a table where a slicer is created to select a range of dates. Table is named Matrix

The tables are related to each other, and the new pivot table I created is connected to the slicer.

What I want to achieve is for the pivot table to display the most recent publication date of each publication, but only if the end date of that years publication is greater than or equal to the minimum or first date in the range of dates selected by the slicer.

The measure I tried was: CALCULATE(LASTDATE(Tracker[Pub Date]), FILTER(Tracker, Tracker[End Date]>=FIRSTDATE(Matrix[Reporting Month])))

But it dawned on me that you cannot Calculate the LastDate function.

Is there another way to pull what I need through powerpivot without needing to create another table? Is there something I can do to change the measure so that it works correctly?

Any information would be extremely helpful

 

[Matt Allington]

It's a bit hard to understand fully, but I suspect there is a dates "issue" here. As I can imagine your tables, each publication has a single date on which it is published, but I assume that your date slicer has lots of dates. Let's take a simple example - if you only have 1 publication and it was published on 1/1/2014 and its last day was 31/12/2014. Then a new version launched on 1/1/2015 and is still current. Now if your date table connects the the launch date and you filter on Jan 2014, you will see your book. But if you filter on Feb 2014, you will see nothing.

Have a read of this blog post I wrote some time ago - it might help confirm if this is the problem or not.
Fill Table with Last Survey Result -

also, you can wrap CALCULATE around any formula at all, including Lastdate.

 

[one3nine0]

Your blog does answer a few of my questions. I think the problem was that the table with the dates I was using for my slicer was related to my table with my publishing and ending dates, so it would pull all the entries, rather than filter out by the dates selected.

I find that if I make a disconnected date table slicer that it works as expected.

Thanks for the help.