cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
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
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 Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)