# Compare Campaigns by Number of Days (Not Dates)

• ### Question

• Hi,

I'd like to compare e.g. sales amount of different campaigns by number of days, not by dates and don't know how.

I give you an example for a better understanding.

I have got a fact table with 2 campaigns, C001 and C002:

• C001 starts March 13th 2013 and ends March 19th 2013
• 03/14/2013, Article 1001, SalesAmount 100, Campaign C001 (==> DayNumber 1)
• 03/14/2013, Article 1002, SalesAmount 60, Campaign C001 (==> DayNumber 1)
• 03/15/2013, Article 1001, SalesAmount 250, Campaign C001 (==> DayNumber 2)
• 03/16/2013, Article 1002, SalesAmount 120, Campaign C001 (==> DayNumber 3)
• 03/18/2013, Article 1002, Sales Amount 60, Campaign C001 (==> DayNumber 5)
• 03/19/2013, Article 1001, SalesAmount 50, Campaign C001 (==> DayNumber 6)
• C002 start April 15th 2014 and ends April 20th 2014
• 04/15/2014, Article 1003, SalesAmount 10, Campaign C002 (==> DayNumber 1)
• 04/17/2014, Article 1004, SalesAmount 40, Campaign C002 (==> DayNumber 3)
• 04/18/2014, Article 1003, SalesAmount 30, Campaign C002 (==> DayNumber 4)
• 04/18/2014, Article 1004, SalesAmount 100, Campaign C002 (==> DayNumber 4)
• 04/19/2014, Article 1003, SalesAmount 60, Campaign C002 (==> DayNumber 5)
• 04/19/2014, Article 1004, SalesAmount 60, Campaign C002 (==> DayNumber 5)
• 04/20/2014, Article 1004, SalesAmount 40, Campaign C002 (==> DayNumber 6)
• It is the objective to calculate the DayNumbers according to the corresponding campaign as shown in the brackets above and compare isolated or cumulated sales by DayNumber
• I use a custom calendar table

My current view is to define a calculated column in the fact table by calculating the day difference between the first date of a campaign and the corresponding invoice date. I am familiar with calculating the day difference based on two dates but I was not able to come up with a solution to my specific problem, e.g. taking a specific campaign into account.

Thank you very much for any help regarding a formula to calculate the DayNumber of sales for a specific campaign.

Chiemo

P.s. I provide a dropbox link to an example Excel 2013 file: CampaignComparison.xlsx

Thursday, April 24, 2014 2:55 PM

• Hi Chiemo,

This should do it as a calculated column:

=[InvoiceDate]-calculate(min([InvoiceDate]);Filter(Table1;[Campaign]=earlier([Campaign])))+1

The calculate-part identifies the earlist invoice date for the campaign of the current record.

I hope this helps!

Regards,

Julian

• Edited by Thursday, April 24, 2014 3:58 PM
• Marked as answer by Friday, April 25, 2014 2:30 PM
Thursday, April 24, 2014 3:57 PM

### All replies

• Hi Chiemo,

This should do it as a calculated column:

=[InvoiceDate]-calculate(min([InvoiceDate]);Filter(Table1;[Campaign]=earlier([Campaign])))+1

The calculate-part identifies the earlist invoice date for the campaign of the current record.

I hope this helps!

Regards,

Julian

• Edited by Thursday, April 24, 2014 3:58 PM
• Marked as answer by Friday, April 25, 2014 2:30 PM
Thursday, April 24, 2014 3:57 PM
• Hi Julian,

Perfect! Your solution worked like a charm.

I just had to add "1. *" right after the "="-sign and your formula produced the day number.

Thank you very much and best regards,

Chiemo

Friday, April 25, 2014 2:29 PM