Running Sum by Week for selected years

[S10TVR]

I am trying to set up a running sum across two dimensions:

1.  A specified number of weeks (1 in the example shown below)

2.  Year (3 in the example shown below)

Both the years required and the number of weeks are selected using a drop down filter.

When viewed in a table containing both the WeekNo and CumulWeekNo the right rows and values are picked out.

(please see left hand table - for example: WeekNo = 2: values for 3 selected years are shown).   I want to sum these and show the answer by WeekNo.

When viewing a table containing only WeekNo, instead of seeing the sum of the years selected, I only see the value for the latest year (see the left Table, below).  In the example:  for WeekNo = 2   I want to see the answer 9+8+12 = 29 instead of 12 (which is the answer for the latest CumulWeekNo).

The code I am using is:

NoNewSel_Weeks2 = Calculate(sumx('Conversion',[NoNew]),
                                    filter(ALLEXCEPT('Conversion',Conversion[CategoryDesc],Conversion[SubCategoryDesc]),
                                    'Conversion'[CumulWeekNo]<=MAX('Conversion'[CumulWeekNo]) &&
                                    'Conversion'[CumulWeekNo]>(Max('Conversion'[CumulWeekNo])-

                                    SELECTEDVALUE(New_AverageOverWeeks[NoWeeks]))))
   

Very many thnaks in advance

Stephen

[S10TVR]

That works perfetly.  Very very many thanks.

I have been trying to work out how to do this for a long time.

Best regards

Stephen

[v-sihou-msft · Accepted solution]

@S10TVR

In your formula, you use MAX('Conversion'[CumulWeekNo]) to specify the range in filter. If you don't put the 'Conversion'[CumulWeekNo] column in your visual, it will always get the max CumulWeekNo under each WeekNo. This is the reason why it always get the latest year result. 

To get your expected result, you should write your formula like below: 

NoNewSel_Weeks_final =
SUMX (
    SUMMARIZE (
        'Conversion',
        'Conversion'[Year],
        "NoNewSel_Weeks3", [NoNewSel_Weeks2]
    ),
    [NoNewSel_Weeks3]
) 

See my sample below:

Regards,