Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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..!
Solved! Go to Solution.
Hi @Anonymous ,
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 ) )
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 @Anonymous ,
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 @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 = 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 @Anonymous ,
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 @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
Hi @Anonymous ,
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 @Anonymous ,
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.
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 @Anonymous ,
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 ) )
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |