Hello,
I'm trying to find a formula that will calculate a static, nonrolling, 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 NonRolling, 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 NonRolling, 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 GHI are dragged down Rows 316 Columns GHI.
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 NonRolling/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..!
Solved! Go to Solution.
Hi @kordelburnett ,
Your formula works fine with the filter on my side, this formula had considered the outer slicers.
Nonrolling 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 ) )
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.
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 ) )
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.
Hi @vlidmsft ,
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]) )
Nonrolling 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/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
Hi @kordelburnett ,
Your formula works fine with the filter on my side, this formula had considered the outer slicers.
Nonrolling 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 ) )
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.
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.
Hi @vlidmsft ,
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
Hi @kordelburnett ,
Sure, we will make explatnation of following formula:
Nonrolling 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 rowlevel 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?
Nonrolling 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.
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.
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.
It’s the start of a new Super User season! Learn all about the new Super Users and brandnew tiered recognition system.
User  Count 

248  
64  
49  
46  
37 
User  Count 

346  
77  
68  
60  
59 