I can mimic SQL Lag but not SQL Lead... Dax help please!

[Anonymous]

Hello,

I'm trying to mimic the SQL function LEAD in Power BI.  I have a table representing emails that have been sent to customers.  There is a column for CustomerID, a column called Sequence which represents each email received by the customer in sequential order (there is a sequence of nine emails that each new customer gets, so there's Email 1, Email 2 etc), and the date that they received the email.

The fourth column, Date of Next Email, is what I want to generate.  In SQL I would use Lead to do this.

CustomerID	Sequence	DateOfEmail	DateOfNextEmail
A	1	2021-01-01	2021-01-07
A	2	2021-01-07
B	1	2021-01-02	2021-01-08
B	2	2021-01-08	2021-01-23
B	3	2021-01-23

I have found a way to get the *previous* email date (i.e. Lag), but that's no use to me.  To get the previous email date, I use the method given here: From SQL to DAX- ‘Lead’ and ‘Lag’ window functions – Gal Love Data (xuanalytics.com)

i.e. 

calculate (max(DateOfEmail),
filter(tablename,
CustomerID=earlier(CustomerID)
&&
Sequence<earlier(Sequence)
))

I thought that it would be easy to change it from Lag to Lead - I thought I could change the < to a >.  However, when I do this, the calculated column populated with the LAST email date for each customer.  Not the next, but the last.  

The article I linked to suggests that this method works for both Lag and Lead, but the author only gave the syntax for Lag.

Any suggestions much appreciated!

[amitchandak · Accepted solution]

@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])

[Anonymous]

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! 

[Anonymous]

Maybe ignore that - one of my date columns got reformatted as a US date, which confused everything.

[Anonymous]

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!

[Anonymous]

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).