Forum Discussion
Helper IDax to Sql, Windowing / Lag function (Over partition by equivalent in Dax)
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.
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])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.
3 Replies
- Phil_Seamark
Microsoft Employee
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])
mleepinHi 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.
- mleepin
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.