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.
Hi ,
I want to have rolling 13 weeks in a calculated column i.e current week plus future 12 weeks. I have to show other data accordingly.I have a date column created in calendar table which is created from a date column from other table. When I am using the query editor filter "IN the next 12 weeks" , the data is coming incorrect. Please help me solving this issue.
Thanks
Solved! Go to Solution.
Hi @Anonymous,
Please check the following steps as below.
1. Create a calculated table and create two calcualted columns in it.
dimtime = CALENDAR(MIN('sales'[date]),MAX('sales'[date]))
weeknum = WEEKNUM('dimtime'[Date])
year week = YEAR(dimtime[Date])*100+WEEKNUM(dimtime[Date])
2. Create a new calculated table to get the result as we need.
result = var yearweekt = YEAR(TODAY())*100+WEEKNUM(TODAY()) var yearweek12= YEAR(TODAY()+84)*100+WEEKNUM(TODAY()+84) return CALCULATETABLE(dimtime,yearweekt<=dimtime[year week] && dimtime[year week]<=yearweek12)
For more details, please check the pbix as attached.
Regards,
Frank
Hi @Anonymous,
Could you please share your sample data and excepted result to me?
Regards,
Frank
Hi Frank,
I have a calendar table made out of a minimum and maximum due date from sales table. This table contains date for 2 quarters at one time and keep updating regularly eg Q4-18 and Q1-19, Q1-19 and Q2-19, Every quarter the dates update. I need to make a week column in the Calendar table which gives me current week and 12 weeks as the output irresepective of the year.
For example i want output currently as Wk 46,47,48,49,50,51,52,53,01,02,03,04,05(I have dates from 1st oct-2018 to 31st march 2019 in calendar table). Next week it should be changed to Wk 47till wk 06.
Thanks
Hi @Anonymous,
Please check the following steps as below.
1. Create a calculated table and create two calcualted columns in it.
dimtime = CALENDAR(MIN('sales'[date]),MAX('sales'[date]))
weeknum = WEEKNUM('dimtime'[Date])
year week = YEAR(dimtime[Date])*100+WEEKNUM(dimtime[Date])
2. Create a new calculated table to get the result as we need.
result = var yearweekt = YEAR(TODAY())*100+WEEKNUM(TODAY()) var yearweek12= YEAR(TODAY()+84)*100+WEEKNUM(TODAY()+84) return CALCULATETABLE(dimtime,yearweekt<=dimtime[year week] && dimtime[year week]<=yearweek12)
For more details, please check the pbix as attached.
Regards,
Frank
Hi Frank,
Thank you for the solution. I have one more thing, i am not able to put the weeks in order as 13 weeks , like 46,47,48,49,50,51,52,53,01,02,03,04,05. It is coming now as 01,02,03,04,05,46,47.....53. Is there any way possible that we can put them in order as i have shown above.
Thanks
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 |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |