CODEWITHSUNDEEP
powerbi
jk7575
jk7575

Reputation: 11

PowerBI - Time Lag Calculation in Dates, using Query Editor

I'm new to Power BI Desktop, coming from Excel.

In the query editor, I would like to create a new column in a table with the difference in time/date from one record to the next, by a separate grouping column (device). An example explains it better. Here's the starting point for the data, with one column for the device id, and another for the Date of the event.

Device   Date  
A        5/1/2016  
B        5/1/2016  
C        5/2/2016  
A        5/4/2016  
B        5/5/2016  
A        5/10/2016  
B        5/9/2016  
C        5/12/2016

I would like to group by Device and Sort by Date, then calculate the differences, to make something like this:

Device   Date       Lag   
A        5/1/2016   (null)   
A        5/4/2016   3  
A        5/10/2016  6  
B        5/1/2016   (null)  
B        5/5/2016   4  
B        5/9/2016   4  
C        5/2/2016   (null)  
C        5/12/2016  10

What's the best way to do this in Power BI query editor?

Thanks for the help!

Upvotes: 1

Views: 9482

Answers (1)

Ravi Sankar Raju
Ravi Sankar Raju

Reputation: 2958

Here's a solution. But it does not involve Query editing.

we're gonna create a calculated column and do it the dax way.

1) In your Fields pane, right click on the table and Select "New Column"

2) In the formula bar, type in the formula below. replace TableName with your table name

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

3) Select Device, Date and the LagColumn in your report now. Choose "Table" option from the visualization panel.

Explanation of the formula -

1) The new lagColumn is the DATEDIFF in days of two entities.

2) First argument is the DATE field of the current row

3) Second argument is the maximum date value of all the dates that are less than the current row's date and that belongs to the same device as the current row. (EARLIER helps you retrieve the value of the current row in the previous context.)

read more here

1) EARLIER - https://msdn.microsoft.com/en-us/library/ee634551.aspx

2) Row Context and Filter Context - https://www.sqlbi.com/articles/row-context-and-filter-context-in-dax/

Screenshot of a similar use-case:

enter image description here

Here,

  • AccountAlternateKey corresponds to Date
  • ParentAccountAlternateKey corresponds to Device, and
  • LagColumn is LagColumn

Note - For every group, lag of the first column is the first column itself. If you want it to be NULL, you can check if ISBLANK(CALCULATE....) is true and then make it null. that just adds a little bit complexity to the formula..

Proposing DAX solution since i don't think there is a query way to deal with this, AFAIK.

Let me know if you have any more questions..

Upvotes: 4

Related Questions