CODEWITHSUNDEEP
powerbidaxpowerquery
Quynh-Mai Chu
Quynh-Mai Chu

Reputation: 155

Time Lag Calculation in Dates/Datetime by using DAX

I am trying to reproduce this example. I use this DAX expression

LagColumn = DATEDIFF(
            CALCULATE(MAX(MyTable[Date]), 
                FILTER(MyTable, 
                    MyTable[Device] = EARLIER(MyTable[Device]) && 
                    MyTable[Date] < EARLIER(MyTable[Date])
                )
            ), MyTable[Date]

        , DAY
        )

However, this calculation does not work when you have two same values (I modified the input of the example):

enter image description here

It should be 0 instead of 3. How can I solve this problem? It seems that there is no Lag function in DAX.

Upvotes: 0

Views: 3585

Answers (1)

Andrii
Andrii

Reputation: 646

MyTable[Date] < EARLIER(MyTable[Date]) condition is looking for rows with the Date column values before the Date value it's currently iterating over. So when the engine is at row 3, the FILTER function will still (just like it did at row 2) leave only the first row.

Not sure about the particularities of your application, but if it's some sort of a log, you can simply add an index column and use it in your FILTER function:

LagColumn = DATEDIFF(
            CALCULATE(MAX('MyTable'[Date]), 
                FILTER('MyTable', 
                    'MyTable'[Device] = EARLIER('MyTable'[Device]) && 
                    'MyTable'[EntryIndex] < EARLIER ('MyTable'[EntryIndex])
                )
            ),'MyTable'[Date]

    , DAY
    )

Upvotes: 1

Related Questions