cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Pauwnrajpp Member
Member

Lead and Lag in DAX

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.

 

 Units_PreviousValue.PNG

 

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

 

Regards,

Pradeep

1 ACCEPTED SOLUTION

Accepted Solutions
d_gosbell Senior Member
Senior Member

Re: Lead and Lag in DAX

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
2 REPLIES 2
d_gosbell Senior Member
Senior Member

Re: Lead and Lag in DAX

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
Pauwnrajpp Member
Member

Re: Lead and Lag in DAX

Thank you d_gosbell. Fantastic solution that you have provided.