cancel
Showing results for
Did you mean:  Helper II

## Replicate LAG T-SQL function logic

Hello,

I was hoping to get an answer to this problem both in DAX and M.

I am trying to replicate LAG T-SQL function below and return number of days since the last order

```SELECT
CustomerID
, SalesOrderID
, CAST(OrderDate as DATE) AS OrderDate
, DATEDIFF(d,LAG(OrderDate) OVER (PARTITION BY CustomerID ORDER BY SalesOrderID), OrderDate) as DaysSinceLastOrder

We are trying to get here is the number of days since the last order.

e.g. 7/22/2005 to 7/22/2007 is 730 days and

7/22/2007 to 11/4/2007 is 105 days

 CustomerID SalesOrderID OrderDate DaysSinceLastOrder 11000 43793 7/22/2005 NULL 11000 51522 7/22/2007 730 11000 57418 11/4/2007 105 11001 43767 7/18/2005 NULL 11001 51493 7/20/2007 732 11001 72773 6/12/2008 328 11002 43736 7/10/2005 NULL 11002 51238 7/4/2007 724 11002 53237 8/27/2007 54 11003 43701 7/1/2005 NULL 11003 51315 7/9/2007 738 11003 57783 11/11/2007 125 11004 43810 7/26/2005 NULL 11004 51595 7/26/2007 730 11004 57293 11/2/2007 99

I started putting together a DAX code but it returns total number of days which for the exaple above for the 3 row instead of 105 return  835 (730+105).

Spoiler
```  = DATEDIFF(
CALCULATE(MIN(LAGfunction[OrderDate]), ALLEXCEPT(LAGfunction, LAGfunction[CustomerID]) ),
CALCULATE(MAX(LAGfunction[OrderDate]), ALLEXCEPT(LAGfunction,  LAGfunction[CustomerID], LAGfunction[SalesOrderID]) ),
DAY
)``` Thank you

2 ACCEPTED SOLUTIONS  Community Support

After my research, you can do these follow my steps as below:

Step1:

use rankx function to add a group rank column

`group rank = RANKX(FILTER(LAGfunction,LAGfunction[CustomerID]=EARLIER(LAGfunction[CustomerID])),LAGfunction[SalesOrderID],,ASC)`

Step2:

use eralier function to add the result column

`Column = DATEDIFF(CALCULATE(MAX(LAGfunction[OrderDate]),FILTER(LAGfunction,LAGfunction[CustomerID]=EARLIER(LAGfunction[CustomerID])&&LAGfunction[group rank]=EARLIER(LAGfunction[group rank])-1)),LAGfunction[OrderDate],DAY)`

Result: here is pbix, please try it.

https://www.dropbox.com/s/8ivwqgzxqmk7byg/Replicate%20LAG%20T-SQL%20function%20logic.pbix?dl=0

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.  Super User

Hi,

Try this calculated column formula

=if(ISBLANK(CALCULATE(MAX(Data[OrderDate]),FILTER(Data,Data[CustomerID]=EARLIER(Data[CustomerID])&&Data[OrderDate]<EARLIER(Data[OrderDate])))),BLANK(),[OrderDate]-CALCULATE(MAX(Data[OrderDate]),FILTER(Data,Data[CustomerID]=EARLIER(Data[CustomerID])&&Data[OrderDate]<EARLIER(Data[OrderDate]))))

Hope this helps. Regards,
Ashish Mathur
http://www.ashishmathur.com
2 REPLIES 2  Super User

Hi,

Try this calculated column formula

=if(ISBLANK(CALCULATE(MAX(Data[OrderDate]),FILTER(Data,Data[CustomerID]=EARLIER(Data[CustomerID])&&Data[OrderDate]<EARLIER(Data[OrderDate])))),BLANK(),[OrderDate]-CALCULATE(MAX(Data[OrderDate]),FILTER(Data,Data[CustomerID]=EARLIER(Data[CustomerID])&&Data[OrderDate]<EARLIER(Data[OrderDate]))))

Hope this helps. Regards,
Ashish Mathur
http://www.ashishmathur.com  Community Support

After my research, you can do these follow my steps as below:

Step1:

use rankx function to add a group rank column

`group rank = RANKX(FILTER(LAGfunction,LAGfunction[CustomerID]=EARLIER(LAGfunction[CustomerID])),LAGfunction[SalesOrderID],,ASC)`

Step2:

use eralier function to add the result column

`Column = DATEDIFF(CALCULATE(MAX(LAGfunction[OrderDate]),FILTER(LAGfunction,LAGfunction[CustomerID]=EARLIER(LAGfunction[CustomerID])&&LAGfunction[group rank]=EARLIER(LAGfunction[group rank])-1)),LAGfunction[OrderDate],DAY)`

Result: here is pbix, please try it.

https://www.dropbox.com/s/8ivwqgzxqmk7byg/Replicate%20LAG%20T-SQL%20function%20logic.pbix?dl=0

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Announcements #### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day! #### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference. Top Solution Authors
Top Kudoed Authors
Users online (1,640)