topic Re: Using Earlier() command for lag in DAX Commands and Tips

Using Earlier() command for lag

Anmol — Wed, 27 May 2020 16:01:08 GMT

I am having a hard time to get a expected output on a calculated column, i know how to get it through sql though using window function(lag). Here is the sql command:

select

(select lag(input_priority) over (partition by task_id order by task_id, request_datetime) as prv_input_priority

, input_priority

, convert(date, request_datetime) as dateField

from task_log

This will sort the data first on 'Task id' and then on 'request_datetime', after which will print the previous value of 'input_priority'.

On DAX i'm trying Earlier(), but having a very hard time to get the expected output.

Re: Using Earlier() command for lag

AlB — Wed, 27 May 2020 16:21:02 GMT

Hi Anmol

Can you show a sample of the table you are using and explain with an example based on that data/table what you would need?

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers

Re: Using Earlier() command for lag

Anmol — Wed, 27 May 2020 22:35:12 GMT

That's how the data looks like, im just looking for a calculated column that gives previous 'input_priority' only after sorting the data on 'task_id' and 'request_datetime'.

Re: Using Earlier() command for lag

Anonymous — Thu, 28 May 2020 05:36:41 GMT

HI Anmol ,

Let me know if this works for you.

Create Calculated Columns

Previous_Value = CALCULATE(

MAX('Table'[Input_Priority]),

FILTER('Table','Table'[Task_Id] = EARLIER('Table'[Task_Id]) && 'Table'[Request_DateTime] < EARLIER('Table'[Request_DateTime] )))

RANK_ = RANKX(

FILTER('Table',

'Table'[Task_Id] = EARLIER('Table'[Task_Id])

'Table'[Request_DateTime]

,,ASC,Dense)

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)