Forum Discussion
Dynamic Past 5 weeks
Hi! Is there a way to have dynamically have only 5 weeks in a separate calculated table? I have one fact table, one Date Dim. I am trying to create a calculated table which will have values of on...
Sample data would help tremendously:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Need: How to make the Year and Week selection dynamic. As of now the 35th Week is hard coded to select past 5 week's data from that week. - Please help! - Thank you!
Please find the below as Data set
| Year | Week day | Week | Prd | Sold | WeekName | SORT_ORDR |
| 2017 | 8/28/2017 | 35 | Prd 5 | 24 | Week 34 Position | 6 |
| 2017 | 8/28/2017 | 35 | Prd 3 | 6 | Week 34 Position | 6 |
| 2017 | 8/28/2017 | 35 | Prd 4 | 3 | Week 34 Position | 6 |
| 2017 | 8/28/2017 | 35 | Prd 1 | 2 | Week 34 Position | 6 |
| 2017 | 8/28/2017 | 35 | Prd 2 | 1 | Week 34 Position | 6 |
| 2017 | 8/28/2017 | 35 | Prd 1 | 17 | Week 35 | 1 |
| 2017 | 8/28/2017 | 35 | Prd 2 | 15 | Week 35 | 1 |
| 2017 | 8/28/2017 | 35 | Prd 3 | 14 | Week 35 | 1 |
| 2017 | 8/28/2017 | 35 | Prd 4 | 10 | Week 35 | 1 |
| 2017 | 8/28/2017 | 35 | Prd 5 | 5 | Week 35 | 1 |
| 2017 | 8/21/2017 | 34 | Prd 2 | 31 | Week 34 | 2 |
| 2017 | 8/21/2017 | 34 | Prd 4 | 30 | Week 34 | 2 |
| 2017 | 8/21/2017 | 34 | Prd 1 | 30 | Week 34 | 2 |
| 2017 | 8/21/2017 | 34 | Prd 3 | 8 | Week 34 | 2 |
| 2017 | 8/21/2017 | 34 | Prd 5 | 1 | Week 34 | 2 |
| 2017 | 8/14/2017 | 33 | Prd 4 | 38 | Week 33 | 3 |
| 2017 | 8/14/2017 | 33 | Prd 1 | 32 | Week 33 | 3 |
| 2017 | 8/14/2017 | 33 | Prd 2 | 21 | Week 33 | 3 |
| 2017 | 8/14/2017 | 33 | Prd 3 | 15 | Week 33 | 3 |
| 2017 | 8/14/2017 | 33 | Prd 5 | 5 | Week 33 | 3 |
| 2017 | 8/7/2017 | 32 | Prd 1 | 33 | Week 32 | 4 |
| 2017 | 8/7/2017 | 32 | Prd 4 | 26 | Week 32 | 4 |
| 2017 | 8/7/2017 | 32 | Prd 2 | 25 | Week 32 | 4 |
| 2017 | 8/7/2017 | 32 | Prd 3 | 6 | Week 32 | 4 |
| 2017 | 8/7/2017 | 32 | Prd 5 | 2 | Week 32 | 4 |
| 2017 | 7/31/2017 | 31 | Prd 2 | 39 | Week 31 | 5 |
| 2017 | 7/31/2017 | 31 | Prd 1 | 33 | Week 31 | 5 |
| 2017 | 7/31/2017 | 31 | Prd 4 | 28 | Week 31 | 5 |
| 2017 | 7/31/2017 | 31 | Prd 3 | 11 | Week 31 | 5 |
| 2017 | 7/31/2017 | 31 | Prd 5 | 5 | Week 31 | 5 |
Expected soln as below Pivot,
| Sum of Sold | Column Labels | ||||||
| Row Labels | Week 34 Position | Week 31 | Week 32 | Week 33 | Week 34 | Week 35 | Grand Total |
| Prd 1 | 2 | 33 | 33 | 32 | 30 | 17 | 147 |
| Prd 2 | 1 | 39 | 25 | 21 | 31 | 15 | 132 |
| Prd 3 | 6 | 11 | 6 | 15 | 8 | 14 | 60 |
| Prd 4 | 3 | 28 | 26 | 38 | 30 | 10 | 135 |
| Prd 5 | 24 | 5 | 2 | 5 | 1 | 5 | 42 |
| Grand Total | 36 | 116 | 92 | 111 | 100 | 61 | 516 |