locked
Compare Campaigns by Number of Days (Not Dates) RRS feed

  • 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

Answers

  • 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 Julian Wissel Thursday, April 24, 2014 3:58 PM
    • Marked as answer by Chiemo 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 Julian Wissel Thursday, April 24, 2014 3:58 PM
    • Marked as answer by Chiemo 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