Return Second to Last Date

[e9jensen]

I am trying to find the second to last invoice date in a sales table to calculate whether it has been twelve months since the last sale or a particular good to a given customer. For example, we sold something to Customer X on August 31, 2013 and the previous sale before that was July 1, 2012, which is the value I want returned to consider it "New". The formula below returns a value of August 31, 2013 for the date. Last Sale Date:=CALCULATE(LASTDATE(GMData[Invc Invoice Date]),DATESBETWEEN(Calendar[DateKey],BLANK(),LASTDATE(Calendar[DateKey])),ALL(Calendar))

 

[miguel.escobar]

Hey!

Do you think that you could share a workbook with dummy data just to understand better your model?

 

[e9jensen]

Here is a link to some sample data. There are two tabs with data which are separate tables in powerpivot. Thank you for your help https://skydrive.live.com/redir?page=view&resid=FB7D018B14EDA209!1444&authkey=!AIg4hxPMiyfZqJs

 

[miguel.escobar]

question, what Power Pivot are you using? do you have the TOPN() Function in your formula list?

 

[e9jensen]

I am using 2010 and I have the TOPN() function in my list.

 

[miguel.escobar]

I'm going to be working on this but just to give you an idea of the approach that I have in mind:
- Use MINX() to get the 2nd to last invoice date
- TOPN() in order to just get the 2 newer invoice dates
- ALL() In order to override the current dates selection from the slicer

Hope this gives you an idea.

 

[e9jensen]

Thanks for the tip. I will give it a shot too using your suggestions. I appreciate the help.