Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
d_gosbell
Super User
Super User

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

View solution in original post

5 REPLIES 5
d_gosbell
Super User
Super User

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

@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.?

 

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
Not applicable

@d_gosbell  This is awesome!! Thank you!!

 

Anonymous
Not applicable

Thank you d_gosbell. Fantastic solution that you have provided.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.