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 only 5 week dates as per the selection made in Date Dim. If I click one date value in the Date Dim, this calculated table should bring values of 5 week dates past to that selected dates.
Main need is, We are trying to see Top 5 products with past 5 week's status of the same product. User will select the week as parameter, the result should display the status of the products for past 5 weeks from the selected week.
Any idea please help! - Thank you!
2 Replies
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
