Is this even possbile?

[hgzelaya]

hi everyone! i have the following table

ID	DATE	ITEMS PURCHASED	AVERAGE OF ITEMS PURCHASED IN THE LAST 2 MONTH BY ID
1	mar-21	10	20
1	abr-21	20	20
1	may-21	20	20
2	jun-21	20	30
2	jul-21	30	30
2	ago-21	30	30

What i need is a calculated column that shows me the average of items purchased in the last 2 dates by id. 

For example id#1 would be 20 items purchased and for id#2 would be 30. I'll leave the following link with the containing data below. Thank you! 🙂

https://docs.google.com/spreadsheets/d/1x3dLSifBYHCcuZTJ6UFaiFKRTdWjriAzu7f08TeioLI/edit?usp=sharing

[Ashish_Mathur]

Hi,

I do not understand your end result.  For ID1 in abr-21, how can the result be 20 (should it not be 15?).  Also, such a calculation should typically be a measure (not a calculated column).  Why do you want this to be a calculated column formula?

[mahoneypat · Accepted solution]

Once you convert your mmm-yy column to a Date type, you can use this column expression to get your result.

Last Two Avg =
VAR thisID = Items[ID]
VAR Last2Rows =
    TOPN ( 2, FILTER ( Items, Items[ID] = thisID ), Items[DATE], DESC )
RETURN
    AVERAGEX ( Last2Rows, Items[ITEMS PURCHASED] )

Pat

[hgzelaya]

Worked as needed. Thank you very much!