Forum Discussion
Custom WEEKNUM - Start Week 0 mid year
We have two campaigns. One starts at the end of May (21 weeks from 1/1/2020) and the other at the end of August (34 weeks from 1/1/2020).
In my Calendar table i have:
Hi Anonymous ,
We can create a week table and two columns to meet your requirement.
1. Create a normal date table and a weeknum column.
Date table = CALENDAR("2019/1/1","2020/12/31")weeknum = WEEKNUM('Date table'[Date],2)2. Create a week column.
Week table = GENERATESERIES(1,53)3. Then we can create two week columns in week table, One starts at the 21 and the other at the 34.
Fiscal Week1 = VAR __fw = [Value] - 21 + 1 RETURN IF(__fw<=0,53+__fw,__fw)Fiscal Week2 = VAR __fw = [Value] - 34 + 1 RETURN IF(__fw<=0,53+__fw,__fw)4. At last we can add two columns to get the week 1 and week 2.
Week 1 = var _week = CALCULATE(SUM('Week table'[Fiscal Week1]),FILTER('Week table','Week table'[Value]='Date table'[weeknum])) return "Week" &" "&_weekWeek 2 = var _week = CALCULATE(SUM('Week table'[Fiscal Week2]),FILTER('Week table','Week table'[Value]='Date table'[weeknum])) return "Week" &" "&_weekIf it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
3 Replies
- amitchandak
Super User
Anonymous , there are overlapping ranges. I doubt you can create like that. You can actually create an independent table with these campaigns and date range and use those in filter based on selection.
like we do in Dynamic segmentation : https://www.youtube.com/watch?v=CuczXPj0N-k
Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos. 
v-zhenbw-msftCommunity Support
Hi Anonymous ,
We can create a week table and two columns to meet your requirement.
1. Create a normal date table and a weeknum column.
Date table = CALENDAR("2019/1/1","2020/12/31")weeknum = WEEKNUM('Date table'[Date],2)2. Create a week column.
Week table = GENERATESERIES(1,53)3. Then we can create two week columns in week table, One starts at the 21 and the other at the 34.
Fiscal Week1 = VAR __fw = [Value] - 21 + 1 RETURN IF(__fw<=0,53+__fw,__fw)Fiscal Week2 = VAR __fw = [Value] - 34 + 1 RETURN IF(__fw<=0,53+__fw,__fw)4. At last we can add two columns to get the week 1 and week 2.
Week 1 = var _week = CALCULATE(SUM('Week table'[Fiscal Week1]),FILTER('Week table','Week table'[Value]='Date table'[weeknum])) return "Week" &" "&_weekWeek 2 = var _week = CALCULATE(SUM('Week table'[Fiscal Week2]),FILTER('Week table','Week table'[Value]='Date table'[weeknum])) return "Week" &" "&_weekIf it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
Hi v-zhenbw-msft thanks for your solution,
can you help me if I want this as ongoing week number, I mean, imaging week 49 is week 1 for me, I want it this counting for 2 years ongoing. how I can make this?
