Forum Discussion
Helper IIs this even possbile?
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
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
3 Replies
- mahoneypat
Microsoft Employee
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
hgzelayaWorked as needed. Thank you very much!
- Ashish_Mathur
Super User
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?
