Dax to Sql, Windowing / Lag function (Over partition by equivalent in Dax)

[mleepin]

I am coming from sql to dax.  I am hoping for some assistance please.  I am attempting to create a column that has the previous row value (LAG) for a group.

My column calc is as follows : 

Lag_Inv_Score1 = CALCULATE(MAX('Sheet1'[Inv_Score1]),FILTER(Sheet1,'Sheet1'[ID]=EARLIER('Sheet1'[ID]) &&  Sheet1[Date] < EARLIER(Sheet1[Date])))

However my result is not as expected.  For lag columns  Inv Score1  - for group AAA1 I am expecting 6, 5 but I we have 6,6.   I am wondering where I am going wrong.  Any advice here would be most appreciated.

Regards  ML.

[Phil_Seamark · Accepted solution]

HI @mleepin

I think this calculated column is close to what you need.  Your formula was returning the MAX value from all previous values, rather than the preceeding logical row.

Lav_Inv_Score1 = 
VAR myID = 'Sheet1'[ID]
VAR myLastDate = MAXX(FILTER('Sheet1','Sheet1'[ID]=myID && 'Sheet1'[Date] < EARLIER('Sheet1'[Date])),'Sheet1'[Date])
RETURN SUMX(FILTER('Sheet1','Sheet1'[Date] = myLastDate && 'Sheet1'[ID] = myID),'Sheet1'[Inv_Score1])

[Phil_Seamark · Accepted solution]

HI @mleepin

I think this calculated column is close to what you need.  Your formula was returning the MAX value from all previous values, rather than the preceeding logical row.

Lav_Inv_Score1 = 
VAR myID = 'Sheet1'[ID]
VAR myLastDate = MAXX(FILTER('Sheet1','Sheet1'[ID]=myID && 'Sheet1'[Date] < EARLIER('Sheet1'[Date])),'Sheet1'[Date])
RETURN SUMX(FILTER('Sheet1','Sheet1'[Date] = myLastDate && 'Sheet1'[ID] = myID),'Sheet1'[Inv_Score1])

[mleepin · Accepted solution]

Lead seems to go like this:

Lead_Inv_Score1 = VAR myID = Sheet1[ID] VAR myNextDate= MINX(FILTER(Sheet1,Sheet1[ID] = myID  && Sheet1[Date] > EARLIER(Sheet1[Date])), Sheet1[Date]) RETURN SUMX(FILTER(Sheet1, Sheet1[Date] = myNextDate && Sheet1[ID]  = myID), Sheet1[Inv_Score1]).

Regards  ML.

[mleepin]

Hi There,

Thank You  - this works well.  Regards  ML.  If I needed to do the same thing for lead  would the calc be very similar ?  Once again any advice here would be most appreciated.

ML.