DAX for LAG

[ranis1227]

Hello,

Sorry if this redundant. Please link a previous solution if applicable. 

I am looking to running a LAG similar to SQL partitioned by category name and visit number, ordered by unit. 

Here is an example, the highlighed transfer unit is what I am looking for:

The data is huge and SQL takes a while to load with it.  Thank you again. 

[tamerj1 · Accepted solution]

Hi @ranis1227 

Please try

Transfer Unit =
VAR CurrentVisitNumTable =
    CALCULATETABLE ( TableName, ALLEXCEPT ( TableName, TableName[Visit_Number] ) )
VAR CurrentDateTime = TableName[Check_In_Time]
VAR TableAfter =
    FILTER ( CurrentVisitNumTable, TableName[Check_In_Time] > CurrentDateTime )
VAR NextRecordTable =
    TOPN ( 1, TableAfter, TableName[Check_In_Time], ASC )
VAR Result =
    MAXX ( NextRecordTable, TableName[Category_Name] )
RETURN
    Result

[ranis1227]

Outstanding! Thank you very much @tamerj1   I have been spinning my wheels for about a week now on this.  This will help with future lags in our data. 

[tamerj1 · Accepted solution]

Hi @ranis1227 

Please try

Transfer Unit =
VAR CurrentVisitNumTable =
    CALCULATETABLE ( TableName, ALLEXCEPT ( TableName, TableName[Visit_Number] ) )
VAR CurrentDateTime = TableName[Check_In_Time]
VAR TableAfter =
    FILTER ( CurrentVisitNumTable, TableName[Check_In_Time] > CurrentDateTime )
VAR NextRecordTable =
    TOPN ( 1, TableAfter, TableName[Check_In_Time], ASC )
VAR Result =
    MAXX ( NextRecordTable, TableName[Category_Name] )
RETURN
    Result

[ranis1227]

This is exactly what I am looking for.  Thank you! 

[tamerj1]

@ranis1227 
How many rows do you have in this table?

[ranis1227]

Over a 1000.  However, I am filtering some out in power query.

[tamerj1]

When you said huge I though milions 🙂 I was worried about the performance 😅

[ranis1227]

@tamerj1 

Sorry about this. I misinterpreted the issue.  The actual data should look like this:

To Unit	visit_number	check_in_time	From Unit
9A	5016226269	6/24/2022 2:37
12BI	5016226269	7/7/2022 4:40	9A
12BI	5016226269	7/7/2022 22:55	12BI
5JIN	5016226269	7/8/2022 15:24	12BI
12BI	5016226269	7/8/2022 15:32	5JIN
5JIN	5016226269	7/8/2022 17:07	12BI
12BI	5016226269	7/9/2022 18:48	5JIN

I updated your dax on various iterations and could not get it.   Let me know if you have any ideas. Thank you again for the input. Sorry for the confusion.  

[tamerj1]

@ranis1227 
From Unit is the column to be calculated?

[ranis1227]

Correct. 

[tamerj1 · Accepted solution]

Good morning @ranis1227 
Here is a sample file with the solution https://we.tl/t-5GlKHJIedF

From Unit = 
VAR CurrentVisitNumTable =
    CALCULATETABLE ( TableName, ALLEXCEPT ( TableName, TableName[visit_number ] ) )
VAR CurrentDateTime = TableName[Check_In_Time]
VAR TableBefore =
    FILTER ( CurrentVisitNumTable, TableName[Check_In_Time] < CurrentDateTime )
VAR PreviousRecordTable =
    TOPN ( 1, TableBefore, TableName[Check_In_Time] )
VAR Result =
    MAXX ( PreviousRecordTable, TableName[To Unit] )
RETURN
    Result