Forum Discussion
I can mimic SQL Lag but not SQL Lead... Dax help please!
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!
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])
5 Replies
- amitchandak
Super User
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])
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!