Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
Hi,
Try this calculated column formula
=CALCULATE(MAX([SalesFigure]),FILTER(Table1,[Date]<EARLIER([Date])&&[SalesPerson]=EARLIER([SalesPerson])))
Hope this helps.
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! 🙂
User | Count |
---|---|
98 | |
90 | |
78 | |
72 | |
65 |
User | Count |
---|---|
114 | |
98 | |
96 | |
68 | |
67 |