cancel
Showing results for
Did you mean:
Frequent Visitor

## Running Sum by Week for selected years

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]))))

Stephen

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Moderator

## Re: Running Sum by Week for selected years

@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,

2 REPLIES 2
Highlighted
Moderator

## Re: Running Sum by Week for selected years

@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,

Frequent Visitor

## Re: Running Sum by Week for selected years

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

Announcements

Kudos to you if you earned one of these! Check your inbox for a notification.

#### Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (3,617)