Find the Second Highest Value

Hi Petersfield

Sorry for the delay in replying

Please try adding the following 2 calculated columns to your table

Week Sales = CALCULATE(SUM('Table1'[Sales]),ALLEXCEPT('Table1',Table1[Date]))

and

Ranking On Week Sales = 
VAR 
    CurrentWeekSales = 'Table1'[Week Sales]
RETURN 
    COUNTROWS(
        FILTER(
            ALL(Table1[Week Sales]),
            'Table1'[Week Sales] < CurrentWeekSales)
            ) + 1

This assigns a ranking value to each week based on the sum of sales.

You can then build the following calculated measure on your table that use the above column (repeat for 3rd highest week) or just use the column above.

Sum of second highest week = 
CALCULATE(
	SUM('Table1'[Sales]),
	FILTER(
		'Table1',
		'Table1'[Ranking On Week Sales] = 2)
		)