Forum, Tips & Tricks

Learn tips and tricks and engage in discussions with our blog authors here. It’s all for your benefit and learning. 

Forum breadcrumbs - You are here:ForumTips & Tricks: Power BILag time series
You need to log in to create posts and topics.

Lag time series

Power Bi also enables us to lag any time series in a table. I will present you one approach how to do it.

Let´s say we have monthly data for specific employees and we want to lag the time series by 6 months.

First, transform your dates series into number format in new column (sometimes it is easier to work with number of date).  Such is done by:

match = YEAR(Overtimes_lag[date])*12+MONTH(Overtimes_lag[date])

 

Second, create new column which adds 6 to the column created in step 1:

 

match _6lag = Overtimes_lag[match] +6

 

Third, create a copy column of employee ID.

 

Final step is to utilize function LOOKUPVALUE to look for values in the same table based on old date series and newly formed one (in number format - created in step 1. and 2.):

 

LOOKUPVALUE(Overtimes_lag[real_saldo];Overtimes_lag[match_6lag];Overtimes_lag[match];Overtimes_lag[employee_ID];Overtimes_lag[employee_ID_copy])

 

Of course, now it depends if you want to move time series forwards or backwards #Done