Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to Solution.
@s-turn , 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])
@s-turn , 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!
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!
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).
User | Count |
---|---|
97 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
111 | |
96 | |
96 | |
67 | |
63 |