How to retrieve previous row in the same table

[seanpatten11]

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

[Ashish_Mathur]

Hi,

Try this calculated column formula

=CALCULATE(MAX([SalesFigure]),FILTER(Table1,[Date]<EARLIER([Date])&&[SalesPerson]=EARLIER([SalesPerson])))

Hope this helps.

[Anonymous · Accepted solution]

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

[seanpatten11]

That works! Thankyou very much! 🙂