cancel
Showing results for
Did you mean:
Highlighted
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..!

example

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Calculate Non-Rolling 13 Week SUM

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 =
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 )
)```

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.
7 REPLIES 7
Community Support Team

## Re: Calculate Non-Rolling 13 Week SUM

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

```13 WK Confirmation Rate 2 =
VAR t =
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 )
)```

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.
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..

```YTD Confirmation Rate =
DIVIDE(
SUM(‘table 2’[Confirmed Quantity]),
SUM(‘table 2’[Order Quantity])
)```
```Non-rolling 13 WK Confirmation Rate =
Var t =
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/Column A B C D E 1 INPUT 2 Material Year Week of Year Ordered Qty Confirmed Quantity 3 abc123 2019 38 1248 180 4 abc123 2019 37 1008 1008 5 abc123 2019 36 1308 1308 6 abc123 2019 35 4839 3830 7 abc123 2019 34 3926 3926 8 abc123 2019 33 1572 1572 9 abc123 2019 32 3380 2288 10 abc123 2019 31 12 12 11 abc123 2019 30 72 72 12 abc123 2019 29 32 32 13 abc123 2019 28 384 384 14 abc123 2019 27 1080 1080 15 abc123 2019 26 444 200 16 abc123 2019 25 4536 1536 17 abc123 2019 24 6480 432 18 abc123 2019 23 8172 2124 19 abc123 2019 21 333 321 20 abc123 2019 17 6372 1692 21 abc123 2019 16 1620 0 22 abc123 2019 15 1884 716 23 abc123 2019 14 1752 1752 24 abc123 2019 13 1600 300 25 abc123 2019 12 1368 1368 26 abc123 2019 11 180 180 27 abc123 2019 2 648 648 28 abc123 2019 1 33 20 29 ABc234 2018 38 568 526 30 ABc234 2018 37 468 300 31 ABc234 2018 36 686 552 32 ABc234 2018 35 989 900 33 ABc234 2018 34 4522 4500

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

Kordel

Community Support Team

## Re: Calculate Non-Rolling 13 Week SUM

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 =
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 )
)```

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

## Re: Calculate Non-Rolling 13 Week SUM

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.
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

## Re: Calculate Non-Rolling 13 Week SUM

Sure, we will make explatnation of following formula:

```Non-rolling 13 WK Confirmation Rate =
Var t =
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 =
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 =
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.

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

## Re: Calculate Non-Rolling 13 Week SUM

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.

Announcements