topic Re: DAX for LAG in DAX Commands and Tips

DAX for LAG

ranis1227 — Tue, 12 Jul 2022 16:52:11 GMT

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.

Re: DAX for LAG

tamerj1 — Tue, 12 Jul 2022 19:03:55 GMT

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

Re: DAX for LAG

ranis1227 — Tue, 12 Jul 2022 19:12:44 GMT

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

Re: DAX for LAG

tamerj1 — Tue, 12 Jul 2022 19:15:23 GMT

ranis1227
How many rows do you have in this table?

Re: DAX for LAG

ranis1227 — Tue, 12 Jul 2022 19:18:39 GMT

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

Re: DAX for LAG

tamerj1 — Tue, 12 Jul 2022 19:21:02 GMT

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

Re: DAX for LAG

ranis1227 — Tue, 12 Jul 2022 20:25:02 GMT

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.

Re: DAX for LAG

tamerj1 — Tue, 12 Jul 2022 21:28:26 GMT

ranis1227
From Unit is the column to be calculated?

Re: DAX for LAG

ranis1227 — Tue, 12 Jul 2022 22:09:22 GMT

Correct.

Re: DAX for LAG

tamerj1 — Wed, 13 Jul 2022 06:52:03 GMT

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

Re: DAX for LAG

ranis1227 — Wed, 13 Jul 2022 12:39:01 GMT

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.