Forum Discussion
Solved
How to retrieve previous row in the same table
Hi, I have the following table structure and would like to create a calculated column based on the previous months sales for that sales person and I constantly get "Multiple rows are returned", please see structure and formula below:
| Date | SalesPerson | SalesFigure |
| 01/01/2017 | Sales1 | 100 |
| 01/01/2017 | Sales2 | 100 |
| 01/01/2017 | Sales3 | 100 |
| 01/02/2017 | Sales1 | 200 |
| 01/02/2017 | Sales2 | 200 |
| 01/02/2017 | Sales3 | 200 |
| 01/03/2017 | Sales1 | 300 |
| 01/03/2017 | Sales2 | 300 |
| 01/03/2017 | Sales3 | 300 |
Formula:
PreviousMonthsValue = LOOKUPVALUE(Table[SalesFigure], Table[SalesPerson], VALUE(Table[SalesPerson]), Table[Date], DATEADD (Table[Date], -1, MONTH))
Does anyone have any ideas i can try?
Many thanks
Hi seanpatten11,
You can try to use below formula to get the previous month data.
PreviousMonthsValue = LOOKUPVALUE(Table1[SalesFigure],[SalesPerson],[SalesPerson],[Date],DATE([Date].[Year],[Date].[MonthNo]-1,[Date].[Day]))+0
Regards,
Xiaoxin Sheng
3 Replies
Hi seanpatten11,
You can try to use below formula to get the previous month data.
PreviousMonthsValue = LOOKUPVALUE(Table1[SalesFigure],[SalesPerson],[SalesPerson],[Date],DATE([Date].[Year],[Date].[MonthNo]-1,[Date].[Day]))+0
Regards,
Xiaoxin Sheng
That works! Thankyou very much! :)
Hi,
Try this calculated column formula
=CALCULATE(MAX([SalesFigure]),FILTER(Table1,[Date]<EARLIER([Date])&&[SalesPerson]=EARLIER([SalesPerson])))
Hope this helps.
