Forum Discussion
Solved
SUM with a previous value from another column
Hi,
I am wanting to SUM SalesNextYr where TimePeriod = TimePeriod - 100
| TimePeriod | Customer | Ref | SalesCurrentYr | SalesNextYr |
| 201101 | 1 | 100 | 20 | 0 |
| 201101 | 1 | 101 | 20 | 10 |
| 201101 | 2 | 100 | 20 | 10 |
| 201201 | 1 | 101 | 15 | 5 |
| 201201 | 2 | 100 | 5 | 15 |
| 201301 | 1 | 101 | 5 | 0 |
| 201301 | 2 | 101 | 10 | 20 |
SalesPeriod = [SalesCurrentYr] + [SalesNextYr]
CalendarPeriod = [SalesCurrentYr] + Value of previous periods [SalesNextYr]
eg 201201, customer 1, ref 101 = 15 + 10 (SalesNextYr 201101) = 25.
| TimePeriod | Customer | Ref | SalesCurrentYr | SalesNextYr | SalesPeriod | CalendarPeriod |
| 201101 | 1 | 100 | 20 | 0 | 20 | 20 |
| 201101 | 1 | 101 | 20 | 10 | 30 | 20 |
| 201101 | 2 | 100 | 20 | 10 | 30 | 20 |
| 201201 | 1 | 101 | 15 | 5 | 20 | 25 |
| 201201 | 2 | 100 | 5 | 15 | 20 | 15 |
| 201301 | 1 | 101 | 5 | 0 | 5 | 10 |
| 201301 | 2 | 101 | 10 | 20 | 30 | 25 |
Hi RickL,
Try these two formulas, please.
SalesPeriod = [SalesCurrentYr] + Table1[SalesNextYr]
CalendarPeriod = [SalesCurrentYr] + CALCULATE ( SUM ( Table1[SalesNextYr] ), FILTER ( 'Table1', 'Table1'[TimePeriod] = EARLIER ( Table1[TimePeriod] ) - 100 && Table1[Customer] = EARLIER ( Table1[Customer] ) ) )Best Regards,
Dale
1 Reply
v-jiascu-msftMicrosoft Employee
Hi RickL,
Try these two formulas, please.
SalesPeriod = [SalesCurrentYr] + Table1[SalesNextYr]
CalendarPeriod = [SalesCurrentYr] + CALCULATE ( SUM ( Table1[SalesNextYr] ), FILTER ( 'Table1', 'Table1'[TimePeriod] = EARLIER ( Table1[TimePeriod] ) - 100 && Table1[Customer] = EARLIER ( Table1[Customer] ) ) )Best Regards,
Dale