Measure to calculate across rows based on condition

[Tom216]

Hi,

I have the following dataset.  

Weeks     Sales

1               5

2               12

3               1

4               8

5               9

I want to add weeks 1 and 2 sales together if the week 2 is selected in a slicer.  If week 1 is selected then only week 1 should should sum.  I am trying to get my measure to reflect this.

What happens though is the week 2 condition only sums the week 2 sales and doesn't add the week 1 sales.  Is there a way to bring the value from another row and add it to a value on the current row?

There is more to the calculation that stated but this is core.  I have an alternate why to do this using variables but I'm curious if the current row can be added to another row based on a condition?

Thanks

Logic = SUMX('Table1'
  ,if('Table1'[Week] = "1. One" 
    ,CALCULATE(SUM(Table1[Sales]), Table1[Week]="1. One"),
    if('Table1'[Week] = "2. Two" 
    ,CALCULATE(SUM(Table1[Sales]), Table1[Week]="1. One")+CALCULATE(SUM(Table1[Sales]), Table1[Week]="2. Two"))
    ))

[jdbuchanan71 · Accepted solution]

Write a measure just to calculate the sum of sales

wk_sales = SUM ( wk_sales[Sales] )

Then you can write a measure to do a running total for the weeks

wk_totaling = 
VAR MaxWk = MAX ( wk_sales[Week] )
RETURN

CALCULATE( [wk_sales],
    FILTER ( ALL ( wk_sales[Week] ) , wk_sales[Week] <= MaxWk ) )