Lead and Lag in DAX

[Anonymous]

Hi Team,

I have a table which shows representative, Region, Date, Items and No. of Units. I just want to look at the previous date units to for the date.

 

Say Example, For 2018-01-06 -->East-->Jones-->Pencil-->Previous Unit should be the unit is previous date for the same combination.

Regards,

Pradeep

[d_gosbell · Accepted solution]

If you want to do this as a calculated column you could use something like the following

Prev Units = 
var _Region = Table1[Region]
var _Representative = Table1[Representative]
var _item = Table1[Items]
var _orderDate = Table1[OrderDate]
var _prevDate = MAXX(FILTER(Table1, Table1[OrderDate] < _orderDate && Table1[Items] = _item && Table1[Region] = _Region && Table1[Representative] = _Representative), Table1[OrderDate])
var _result = LOOKUPVALUE(Table1[Units], [Region] , _Region, [Representative], _Representative, [Items], _item, [OrderDate] ,_prevDate)
return _result

[longvo]

@d_gosbell  Thanks for this solution. I understand that this gets the 1-period lag record. Is it possible to modify the formula to get 2-period lag, 1-period lead, 2-period lead values etc.?

[d_gosbell]

Going back or forward an abitrary number of periods would not be efficient using the lookup approach. Possibly something like the following by calculating a rank of the dates would work better for that.

https://dax.do/ywC3GzqY6vdq4n/ 

[Anonymous]

@d_gosbell  This is awesome!! Thank you!!

[Anonymous]

Thank you d_gosbell. Fantastic solution that you have provided.