Custom WEEKNUM - Start Week 0 mid year

[Anonymous]

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: 

CampaignWeekNumber = IF (CONTAINS('Leads', 'Leads'[Campaign], 1), "Week" & " " & WEEKNUM('Calendar'[Date].[Date],2) - 21,

IF (CONTAINS('Leads', 'Leads'[Campaign], 2), "Week" & " " & WEEKNUM('Calendar'[Date].[Date],2) - 34 ))

I have a slicer with 'Campaign'. When I choose "1", 'Week of the Year' at Week 0 works well. But when i choose "2", the 'CampaignWeekNumber' does not change.  Image should show Week 0 at 'Week of the Year' = 34 since 'Campaign' = 2

R-ship is 'Campaign' (many) to 'Calendar'(one) by 'Date' column.

It does the same if i do the formula in my 'Campaign' table

Any help please.

Thanks

[v-zhenbw-msft · Accepted solution]

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" &" "&_week

Week 2 = 
var _week = CALCULATE(SUM('Week table'[Fiscal Week2]),FILTER('Week table','Week table'[Value]='Date table'[weeknum]))
return
"Week" &" "&_week

If 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.

[Anonymous]

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?

[amitchandak]

@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.