Week number based on different start dates for each campaign

[Anonymous]

Thanks for taking a look! So far, the solutions I have come across to provide a week number have been based on a static reference. For example, Jan 1 - Jan 7 will always be week 1, year over year. My unique case requires a few different things.

1. Campaigns are launched at different dates each year. For example, campaign A launched Mar 1, 2019 and in 2020, campaign A was launched Feb 4, 2020. So, week 1 will always be different year over year.

2. Historically, business has required that IF a campaign launched mid-week, for calculation purposes, the first week encompasses the partial week PLUS the immediate full week from the next Sunday to Saturday.

    - So, based on Mar 1, 2019 start date, the first week for campaign A in 2019 is from Fri, Mar 1, 2019 - Sat, Mar 9, 2019. This totals 9 days. This time period has to be compared to the first week in 2020 which is from Tue, Feb 4, 2020 - Sat, Feb 15, 2020, for a total of 12 days.

As for the structure of my data, the metadata (campaign name, fiscal year, quarter, start date, etc.) is in a Metadata table. Performance metrics for campaigns are in 2 tables; a Responses table and a Sales data (leads and opportunities) table. Metadata, Responses, and Leads table have a MAC code column that is used to tie them together.

The Responses and Leads table both have a Created Date column that shows when a response and lead was created. Currently, the Metadata table has a one to many relationship on the MAC code column with Responses and Leads table. Somehow, the created date columns need to be evaluated against the start date column to calculate which week number the response/lead correspond to.

Let me know any questions and thanks again for the help!

Regards,

[amitchandak]

@Anonymous , The information you have provided is not making the problem clear to me. Can you please explain with an example.
Do you want static week to work. Of each campaign has how it performed in the first week, 2nd week etc

If you need first week second from the campaign start date,

First of all, have a week start in fact/transaction table

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date])+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date])

Now this you need have date diff with the calendar and use binning/dynamic segmentation

refer

https://www.daxpatterns.com/dynamic-segmentation/

https://radacad.com/grouping-and-binning-step-towards-better-data-visualization
https://www.daxpatterns.com/static-segmentation/
https://www.poweredsolutions.co/2020/01/11/dax-vs-power-query-static-segmentation-in-power-bi-dax-power-query/

Appreciate your Kudos.

[Anonymous]

@amitchandak @dax were you able to look into this?

[Anonymous]

Thanks for giving it a try @amitchandak. Here's some more clarification. I have to be able to show how the campaign does each year by comparing weekly performance. If the campaign started on the same date every year, calculating weekly performance would be a piece of cake. Also, if the first week was 7 days every year, that would also be easy. However, the recurring campaigns launch at different dates every year so the week dates shift depending on when the campaign was launched. For example, campaign "A" has launched every year for the last 3 years. Below are the starting dates:

1. 2017 - Tue, Apr 12, 2017

2. 2018 - Thu, Mar 29, 2018

3. 2019 - Sun, Apr 6, 2019

Furthermore, calculating the first week for each year is not as simple as starting with a date and adding 6 days to get the end date for the first week because of a business rule. I think discussing the business rule will add more confusion so providing the sample end dates based on the above dates:

1. 2017 - Start: Wed, Apr 12, 2017   End: Sun, Apr 22, 2017   Total: 11 days

2. 2018 - Start: Thu, Mar 29, 2018   End: Sun, Apr 7, 2018      Total: 10 days

3. 2019 - Start: Sat, Apr 6, 2019      End: Sun, Apr 13, 2019     Total:   8 days

As you can see above, if the campaigns launch on any day besides a Sunday, the first week will never be 7 days. Week 2 onwards, however, the campaign resumes a Sun - Sat week for a total of 7 days every week until the campaign is over.

It's just the first week calculation that's difficult for me to figure out because of the different start dates every year AND the business rule.

If that doesn't help, can we get on a call?

[dax]

Hi @Anonymous , 

I am not clear about your requirement, if possible could you please inform me more detailed information(such as your expected output and your sample data )? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@dax Thanks for taking a look. Below is the output I'm looking for. 

I've uploaded the pbix file on Google Drive. Let me know if you can't download it yourself.

In the file, I want to use the Campaign Start Date column in the Metadata table to calculate the week number for each row in the Created Date column in AP Leads table and Batch Date column in Responses table. Then I can aggregate the total number of rows for leads and sum the Count column in the Responses table for total responses.

The filters at the top right of the pbix file allow you to select campaigns. The intention is to have each campaign selected be represented by its own line instead of how it's behaving right now.

The complicated bit is this: each year, the campaign has a different start date. Also, the first week length will never be less than 7 days, this is a business rule I cannot ignore. So, based on the Campaign Start Date column, FY19 campaign's 1st week is from Tue, Apr 9, 2019 - Sat, Apr 20, 2019. FY17 campaign's 1st week is from Tue, Jun 27, 2017 - Sat, Jul 8, 2017.

2nd week onwards, the week will always be 7 days and will start on a Sun and end on a Sat.

Please let me know if you have any other questions.

Regards,

[dax]

Hi @Anonymous , 

I find that your week number is custom(instead of calendar), so I suggest you could create a calculated column to re-define the week number, then  create a week number table, create a relationship between week number table and new calculated column, use week number from week number table as x axis, use Campaign Description as legend, which should work.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@dax Thanks. I will give this a shot. I will let you know the results this week, hopefully.