Forum Discussion
I can mimic SQL Lag but not SQL Lead... Dax help please!
Anonymous , These two columns will give you previous and next
maxx(filter(tablename, CustomerID=earlier(CustomerID) && Sequence<earlier(Sequence)),[DateOfEmail])
minx(filter(tablename, CustomerID=earlier(CustomerID) && Sequence >earlier(Sequence)),[DateOfEmail])
Super UserAnonymous , These two columns will give you previous and next
maxx(filter(tablename, CustomerID=earlier(CustomerID) && Sequence<earlier(Sequence)),[DateOfEmail])
minx(filter(tablename, CustomerID=earlier(CustomerID) && Sequence >earlier(Sequence)),[DateOfEmail])
Thank you for your response. Unfortunately, I am having exactly the same problem - the Lag one works perfectly, but the Lead one just brings up the LAST email date for each customer (instead of the next).
Hmm, something weird - I've noticed that the NextEmailDate *does* change, but only when the month changes. So, let's say emails 1-4 are sent in January, the NextEmailDate will show for Emails 1-3 as the date that Email 4 is sent. However, once we get into February, the NextEmailDate is showing as the last date in Fenruary that the customer received an email. I'm completely baffled!
Maybe ignore that - one of my date columns got reformatted as a US date, which confused everything.
OK, it worked! Thanks so much, amitchandak . I got there in the end - you were right all along. I'm glad it was a relatively short bit of syntax too - that gives me hope that I can start to understand it!