cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
kordelburnett Frequent Visitor
Frequent Visitor

Calculate Non-Rolling 13 Week SUM

Hello,

I'm trying to find a formula that will calculate a static, non-rolling, SUM of the previous 13 weeks of ordered units. Once I can SUM the last 13 weeks of the Ordered and Confirmed Quantities, I'll divide the two to find the 13 Week Confirmation Rate for each week of the year.

I've been able to calculate the Rolling 13 Week Confirmation Rate (CR), however now I want to find what the 13 Week CR was in each week of the year. The difference between Rolling and Non-Rolling, is that if you have the Weeks of the year in a vertical table, the Rolling formula would calculate the same for each Week of the year. However with Non-Rolling, the formula will calculate the 13 Week CR for each week of the year, row by row.

To work with, I have a Created On Date, which I have made a Week of Year measure to display the week number in the table. I can also create an end of week date measure if needed for the formula.

Below is the desired output if calculated and simplified in Excel. Essentially what I want to duplicate in Power BI is the SUM of the previous 13 Weeks Order Quantities, for each week of the year. The desired output is highlighted in blue on the right. The formulas in Row 17 Columns G-H-I are dragged down Rows 3-16 Columns G-H-I.

 

Row/Column

A

B

C

D

E

F

G

H

I

1

INPUT

 

OUTPUT

2

Material

Week of Year

Ordered Qty

Confirmed Quantity

 

Week of Year

Ordered Qty

Conf Qty

13 WK Confirmation Rate

3

abc123

38

1248

180

 

38

19305

15892

82%

4

abc123

37

1008

1008

 

37

22593

17248

76%

5

abc123

36

1308

1308

 

36

28065

16672

59%

6

abc123

35

4839

3830

 

35

34929

17488

50%

7

abc123

34

3926

3926

 

34

30423

13979

46%

8

abc123

33

1572

1572

 

33

32869

11745

36%

9

abc123

32

3380

2288

 

32

32917

10173

31%

10

abc123

31

12

12

 

31

31421

8601

27%

11

abc123

30

72

72

 

30

33161

10341

31%

12

abc123

29

32

32

 

29

34689

10569

30%

13

abc123

28

384

384

 

28

36025

11905

33%

14

abc123

27

1080

1080

 

27

35821

11701

33%

15

abc123

26

444

200

 

26

35389

11269

32%

16

abc123

25

4536

1536

 

25

34978

11089

32%

17

abc123

24

6480

432

 

 

=SUM(D3:D15)

=SUM(E3:E15)

=I4/H4

18

abc123

23

8172

2124

 

 

 

 

 

19

abc123

21

333

321

 

 

 

 

 

20

abc123

17

6372

1692

 

 

 

 

 

21

abc123

16

1620

0

 

 

 

 

 

22

abc123

15

1884

716

 

 

 

 

 

23

abc123

14

1752

1752

 

 

 

 

 

24

abc123

13

1600

300

 

 

 

 

 

25

abc123

12

1368

1368

 

 

 

 

 

26

abc123

11

180

180

 

 

 

 

 

27

abc123

2

648

648

 

 

 

 

 

28

abc123

1

33

20

 

 

 

 

 

 

The screenshot attached/below is the desired output and use of the Non-Rolling/Static 13 WK calculation. Please note that the % formula's in the screenshot aren't correct.. my percentage calculation is off when compared to the correct calculation in the excel table example above.

Thanks in advance for any help solving this one..!

 PBI example.JPGexample

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Calculate Non-Rolling 13 Week SUM

Hi @kordelburnett ,

 

Your formula works fine with the filter on my side, this formula had considered the outer slicers.

 

Non-rolling 13 WK Confirmation Rate = 
Var t = 
    ADDCOLUMNS (
        ALLSELECTED( 'Table'),
        "WeekIndex", RANKX ( ALLSELECTED ( 'Table' ),[Week of Year],, DESC )
        )
Var i =
    MAXX (
        FILTER ( t , 'Table' [Week of Year] = MAX ('Table' [Week of Year] ) ),
        [WeekIndex] 
        )
RETURN
    CALCULATE (
        SUM ('Table'[Confirmed Quantity] ),
        FILTER ( t, [WeekIndex] <= i + 12 && [WeekIndex] >= i )
        )
        / CALCULATE (
            SUM ('Table'[Ordered Qty] ),
            FILTER ( t, [WeekIndex] <= i + 12 && [WeekIndex] >= i )
        )

 

26.PNG

 


BTW, pbix as attached.

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
Community Support Team
Community Support Team

Re: Calculate Non-Rolling 13 Week SUM

Hi @kordelburnett ,

 

Based on the table you shared, we can use the following measure to meet your requirement:

 

13 WK Confirmation Rate 2 = 
VAR t =
    ADDCOLUMNS (
        ALLSELECTED ( 'Table2' ),
        "WeekIndex", RANKX ( ALLSELECTED ( 'Table2' ), [Week of Year],, DESC )
    )
VAR i =
    MAXX (
        FILTER ( t, 'Table2'[Week of Year] = MAX ( 'Table2'[Week of Year] ) ),
        [WeekIndex]
    )
RETURN
    CALCULATE (
        SUM ( 'Table2'[Confirmed Quantity] ),
        FILTER ( t, [WeekIndex] <= i + 12 && [WeekIndex] >= i )
    )
        / CALCULATE (
            SUM ( 'Table2'[Ordered Qty] ),
            FILTER ( t, [WeekIndex] <= i + 12 && [WeekIndex] >= i )
        )

16.PNG

 


BTW, pbix as attached.

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
kordelburnett Frequent Visitor
Frequent Visitor

Re: Calculate Non-Rolling 13 Week SUM

Hi @v-lid-msft ,


I've copied your formula to my PBI file and interestingly the 13 Week Confirmation Rate formula is calculating the same result as my YTD Confirmation Rate formula.. See below the formulas, and let me know if you see anything wrong with mine..

comparison 10.11.19.PNG

YTD Confirmation Rate = 
DIVIDE(
    SUM(‘table 2’[Confirmed Quantity]),
    SUM(‘table 2’[Order Quantity])
)
Non-rolling 13 WK Confirmation Rate = 
Var t = 
    ADDCOLUMNS (
        ALLSELECTED( ‘table 2’),
        "WeekIndex", RANKX ( ALLSELECTED ( ‘table 2’ ),[Week of Year],, DESC )
        )
Var i =
    MAXX (
        FILTER ( t , ‘table 2’ [Week of Year] = MAX (‘table 2’ [Week of Year] ) ),
        [WeekIndex] 
        )
RETURN
    CALCULATE (
        SUM (‘table 2’ [Confirmed Quantity] ),
        FILTER ( t, [WeekIndex] <= i + 12 && [WeekIndex] >= i )
        )
        / CALCULATE (
            SUM (‘table 2’ [Order Quantity] ),
            FILTER ( t, [WeekIndex] <= i + 12 && [WeekIndex] >= i )
        )

 

I think the problem might be because I have filters on the report that I didn't previously mention, since I didn't think they would effect the formula.. I have the below slicer filters used on this report.. to help clarify, blue is what the 13 Week Confirmation table should calculate for, and red is what the measure should not calculate for.

Material

Abc123 (filtered on)

Abc234 (filtered out)

Abc345 (filtered out)

Abc456 (filtered out)

Year

2018 (filtered out)

2019 (filtered on)

 

Are there any filter [material] or [year] formulas I need to add to your formula to make mine work? for example, see below. I thought I could get away with the simplified excel table example, but here it is more complicated.

 

Row/ColumnABCDE
1INPUT
2MaterialYearWeek of YearOrdered QtyConfirmed Quantity
3abc1232019381248180
4abc12320193710081008
5abc12320193613081308
6abc12320193548393830
7abc12320193439263926
8abc12320193315721572
9abc12320193233802288
10abc1232019311212
11abc1232019307272
12abc1232019293232
13abc123201928384384
14abc12320192710801080
15abc123201926444200
16abc12320192545361536
17abc1232019246480432
18abc12320192381722124
19abc123201921333321
20abc12320191763721692
21abc12320191616200
22abc1232019151884716
23abc12320191417521752
24abc1232019131600300
25abc12320191213681368
26abc123201911180180
27abc12320192648648
28abc123201913320
29ABc234201838568526
30ABc234201837468300
31ABc234201836686552
32ABc234201835989900
33ABc23420183445224500

 

Appreciate the help.. very close to solving this problem.

Kordel

Community Support Team
Community Support Team

Re: Calculate Non-Rolling 13 Week SUM

Hi @kordelburnett ,

 

Your formula works fine with the filter on my side, this formula had considered the outer slicers.

 

Non-rolling 13 WK Confirmation Rate = 
Var t = 
    ADDCOLUMNS (
        ALLSELECTED( 'Table'),
        "WeekIndex", RANKX ( ALLSELECTED ( 'Table' ),[Week of Year],, DESC )
        )
Var i =
    MAXX (
        FILTER ( t , 'Table' [Week of Year] = MAX ('Table' [Week of Year] ) ),
        [WeekIndex] 
        )
RETURN
    CALCULATE (
        SUM ('Table'[Confirmed Quantity] ),
        FILTER ( t, [WeekIndex] <= i + 12 && [WeekIndex] >= i )
        )
        / CALCULATE (
            SUM ('Table'[Ordered Qty] ),
            FILTER ( t, [WeekIndex] <= i + 12 && [WeekIndex] >= i )
        )

 

26.PNG

 


BTW, pbix as attached.

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Community Support Team
Community Support Team

Re: Calculate Non-Rolling 13 Week SUM

Hi @kordelburnett ,

 

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
kordelburnett Frequent Visitor
Frequent Visitor

Re: Calculate Non-Rolling 13 Week SUM

Hi @v-lid-msft ,

 

Unfortunately your formula in the original and secondary post have not brought me to the same solution as you got in your .pbix file. I'm not sure what else could be the cause of my formula not working.. given that I copied every detail of your formula over to my own file.. I'm not sure what else could be wrong. Could you please walk me through the logic behind how you came to this formula as the answer? I don't understand how RANKX, ADDCOLUMNS, and ALLSELECTED are used to find the result.

 

Perhaps there's a different way to write the formula that I could try? apologies for the lack of detail on how to move forward..

Thanks,

Kordel

Community Support Team
Community Support Team

Re: Calculate Non-Rolling 13 Week SUM

Hi @kordelburnett ,

 

Sure, we will make explatnation of following formula:

 

Non-rolling 13 WK Confirmation Rate = 
Var t = 
    ADDCOLUMNS (
        ALLSELECTED( 'Table'),
        "WeekIndex", RANKX ( ALLSELECTED ( 'Table' ),[Week of Year],, DESC )
        )
Var i =
    MAXX (
        FILTER ( t , 'Table' [Week of Year] = MAX ('Table' [Week of Year] ) ),
        [WeekIndex] 
        )
RETURN
    CALCULATE (
        SUM ('Table'[Confirmed Quantity] ),
        FILTER ( t, [WeekIndex] <= i + 12 && [WeekIndex] >= i )
        )
        / CALCULATE (
            SUM ('Table'[Ordered Qty] ),
            FILTER ( t, [WeekIndex] <= i + 12 && [WeekIndex] >= i )
        )

The first part, we use ALLSELECTED function to get a table which infused by outer slicer, but ignore the row-level content (such as each rows in table visual)

then we add a addtion column to this table, containing a rank index value based on the week of year, such as week 5, 4, 2 will get a rank by 1,2,3

we put the result table into a variable called "t"

Var t = 
    ADDCOLUMNS (
        ALLSELECTED( 'Table'),
        "WeekIndex", RANKX ( ALLSELECTED ( 'Table' ),[Week of Year],, DESC )
        )

Then we want to get the rank index value of now selected date (such as one row in table visual), 

we use Max to get the current week value, then use it to find the index value in the table t

MAXX function is used to get the value from a variable table. (After filter the table t, it will ony return several rows contain same week number, they all have same index value)

Var i =
    MAXX (
        FILTER ( t , 'Table' [Week of Year] = MAX ('Table' [Week of Year] ) ),
        [WeekIndex] 
        )

Now, we have the week index value of current week and a table contain the week index column. we can use the sum function to get value from a filtered table, just use the condition index in greater than current but lessthan than current index plus 12

 

 

CALCULATE (
        SUM ('Table'[Confirmed Quantity] ),
        FILTER ( t, [WeekIndex] <= i + 12 && [WeekIndex] >= i )
        )
        / CALCULATE (
            SUM ('Table'[Ordered Qty] ),
            FILTER ( t, [WeekIndex] <= i + 12 && [WeekIndex] >= i )
        )

Now the logic of this formula is clear, We think the problem may be in the variable i, could you please try to use the following formula?

 

Non-rolling 13 WK Confirmation Rate Version 2 = 
VAR t =
    ADDCOLUMNS (
        ALLSELECTED ( 'Table' ),
        "WeekIndex", RANKX ( ALLSELECTED ( 'Table' ), [Week of Year],, DESC )
    )
VAR i =
    RANKX ( ALLSELECTED ( 'Table' ), CALCULATE ( MAX ( [Week of Year] ) ),, DESC )
RETURN
    CALCULATE (
        SUM ('Table'[Confirmed Quantity] ),
        FILTER ( t, [WeekIndex] <= i + 12 && [WeekIndex] >= i )
        )
        / CALCULATE (
            SUM ('Table'[Ordered Qty] ),
            FILTER ( t, [WeekIndex] <= i + 12 && [WeekIndex] >= i )
        )

Also do not forget to format this measure as a percentage value.

 

4.PNG5.PNG

 


BTW, pbix as attached.

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Community Support Team

Re: Calculate Non-Rolling 13 Week SUM

Hi @kordelburnett ,


How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors