Running Sum by Week for selected years

Question

I am trying to set up a running sum across two dimensions:1.&nbsp; A specified number of weeks (1 in the example shown below)2.&nbsp; Year (3 in the example shown below)Both the years required and the number of weeks are selected using a drop down filter.&nbsp;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:&nbsp;WeekNo = 2: values for 3 selected years are shown).&nbsp;&nbsp; I want to sum these and show the answer by WeekNo.&nbsp;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).&nbsp; In the example:&nbsp; for WeekNo = 2&nbsp; &nbsp;I want to see the answer 9+8+12 = 29 instead of 12 (which is the answer for the latest CumulWeekNo).&nbsp;The code I am using is:NoNewSel_Weeks2 = Calculate(sumx('Conversion',[NoNew]),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; filter(ALLEXCEPT('Conversion',Conversion[CategoryDesc],Conversion[SubCategoryDesc]),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'Conversion'[CumulWeekNo]&lt;=MAX('Conversion'[CumulWeekNo]) &amp;&amp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;'Conversion'[CumulWeekNo]&gt;(Max('Conversion'[CumulWeekNo])-&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECTEDVALUE(New_AverageOverWeeks[NoWeeks]))))&nbsp;&nbsp;&nbsp;Very many thnaks in advance&nbsp;Stephen

v-sihou-msft · Accepted Answer

S10TVR
&nbsp;
In your formula, you use&nbsp;MAX('Conversion'[CumulWeekNo]) to specify the range in filter. If you don't put the&nbsp;'Conversion'[CumulWeekNo] column in your visual, it will always get the max CumulWeekNo&nbsp;under each WeekNo. This is the reason why it always get the latest year result.&nbsp;
&nbsp;
To get your expected result, you should write your formula like below:&nbsp;
&nbsp;
NoNewSel_Weeks_final =
SUMX (
    SUMMARIZE (
        'Conversion',
        'Conversion'[Year],
        "NoNewSel_Weeks3", [NoNewSel_Weeks2]
    ),
    [NoNewSel_Weeks3]
) 
See my sample below:
&nbsp;

&nbsp;

&nbsp;
Regards,

Forum Discussion

Running Sum by Week for selected years

2 Replies

Related content

DAX for Financial Year Running Report Employee

Sorting X- axis based on Month-Year slicer selection

Running Maximum

Running Total For Runs per year

Running Total with three years