SQL Report Builder Same Key has already been added; Update with Cte

Question

I have declared a table with the following variables:

CREATE TABLE #tmp
(
    [Counter] int              
    ,Period Date primary key
    ,VanMe float
)

And I have done some steps to set the VanMe.

The following is the With and the Select statement where the error is occurring:

I narrowed this down to the first part in With because for Report Builder cte.Period and #tmp.Period is considered the same two keys. After I changed that and used the Select statement it still gave me the same error.

Is there a way I can calculate the Avg in the With statement and just call

Select * from #tmp. 

I tried it with the update it did not work at all. It gave me null for the VanMeAvg and the other Avg variables.

Please HELP!

The error that I get is this:

An item with the same key has already been added.

Answer 1

It seems that there are 2 columns with the same name in the select query .Column names should be distinct . The repeated columns are

HfxMetric
,HfxMetric = AVG(HfxMetric) Over (Partition by [Counter])