cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
S10TVR Frequent Visitor
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).

 

RunningTotal.png

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Moderator v-sihou-msft
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:

 

234.PNG

 

123.PNG

 

Regards,

View solution in original post

2 REPLIES 2
Highlighted
Moderator v-sihou-msft
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:

 

234.PNG

 

123.PNG

 

Regards,

View solution in original post

S10TVR Frequent Visitor
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

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

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

Microsoft Implementation for Communities Wins Award

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

Power Platform World Tour

Find out where you can attend!

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