4 Replies Latest reply on May 10, 2017 10:18 AM by Ritesh Bisht

# Opening Weekend Calculation

Hi guys, I have a requirement to calculate opening weekend amount for products launched during the week. For example  - if the product 1 is launched on 03/04/2017 (Thursday) then calculate the total amount from Thursday to first Sunday of that week.. i.e. 03/04 to 03/07.

Product 2 launched on 03/05/2017(Friday) then calculate the total amount from Friday to First Sunday.  i.3. 03/05 to 03/07

Product 3 launched on 03/01/2017 (Monday) then calculate the total amount form Monday to First Sunday i.e. 03/01 to 03/07

The business wants to know what is the sales of week1 from product launch date until First Sunday.

I have attached the sample work book and test data. Any suggestions would be helpful. Thanks, Vandana

• ###### 1. Re: Opening Weekend Calculation

STEP 1

Calculation for first Sunday

First Sunday

=

if [Launch Day of the week]='Mon' then [Launch Date]+6 ELSEIF

[Launch Day of the week]='Tue' then [Launch Date]+5 ELSEIF

[Launch Day of the week]='Wed' then [Launch Date]+4 ELSEIF

[Launch Day of the week]='Thu' then [Launch Date]+3 ELSEIF

[Launch Day of the week]='Fri' then [Launch Date]+2 ELSEIF

[Launch Day of the week]='Sat' then [Launch Date]+1 ELSEIF

[Launch Day of the week]='Sun' then [Launch Date]+7 END

Drag this to rows

STEP 2

Another Calculation

"Till Sunday"=

if [Shopping Date] <= [First Sunday] then 'true' else 'false' end

Drag this to filter Select "True"

STEP 3

Calculation till Sunday

Amount Sum Till Sunday

{ EXCLUDE [First Sunday],[Shopping Date] : SUM([Amount])}

Drag this to rows.

Thanks,

Ritesh

1 of 1 people found this helpful
• ###### 2. Re: Opening Weekend Calculation

Hi, Vandana

You can create a calculation field as shown below and put it to filter.

here is the result when filtering the open weekend only.

Let me know if it works for you. I attached the workbook as well for your reference.

ZZ

1 of 1 people found this helpful
• ###### 3. Re: Opening Weekend Calculation

Hi Ritesh, Thanks for your suggestion! Quick Question - Is there a way I can create a measure so that I can add the opening weekend as well as the overall totals in one visualization like a bar chart. Thanks for your help again! Vandana

• ###### 4. Re: Opening Weekend Calculation

Hi Vandana,

Sure, also please mark it as completed/helpful if it satisfies you !

STEP 1

STEP 2

STEP 3

STEP 4

Drag Launch date and      Till Sunday to Columns

Include Sum to rows

STEP 5

Go to Analysis --> Total --->Add all sub-total

STEP 5

Drag product name to colour (only if needed )