Urgent Help Needed - DAX Measure (or calculated column) to return the second to last invoice date

[ScottTynan]

Hello, 

I have a data set of revenue by period which basically holds invoice data. Date, Customer, invoice item, $ etc. 

I need to show next to items that were invoiced this month what month(date) they were last invoiced for the same item to the same customer.

With the help of google I have created the below but it is returning the most recent date rather than the second to last. I also need it to ignore any filters on the report, visual or page which it is not doing currently.
Any Ideas?

Previous Invoice Date =

calculate(

lastdate('Revenue by Period'[Date])

,

filter(ALL('Revenue by Period'),

[Debtor Number]=selectedvalue('Revenue by Period'[Debtor Number]) &&

[Job Code]=selectedvalue('Revenue by Period'[Job Code])))

[ValtteriN · Accepted solution]

Hi,

Try something like this:

Data:

DAX:

LastInvoiced =

var cdate = MAX(LastInvoiced[I_date]) var _result =

 CALCULATE(MAX(LastInvoiced[I_date]),LastInvoiced[I_date]<cdate,ALL('LastInvoiced'[I_date])) return

 IF(ISBLANK(_result),"NA",_result)

End result:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[ScottTynan]

I'm absolutely bamboozled. 
That seems to have worked, How is it matching the customer and the job code without referencing them at all?

[ValtteriN]

Hi @ScottTynan ,

DAX notices row context so when you apply the column level filters the calculation takes these as filters and considers when was the last invoice date based on those values. I recommend reading this post about the matter to understand this interaction better: https://www.sqlbi.com/articles/row-context-in-dax/