5 Replies Latest reply on Oct 26, 2018 1:33 PM by Deepak Rai

# Using a calculated field in a formula

I need to calculate Revenue per Unit from the first half of 2017 (Jan through June) and multiply it with the number of Visits in 2018. Workbook attached for reference

So basically sum(first-six-month-2017-Revenue) / sum(first-six-month-2017-units) * visits (2018).

How do I write the formula? Would appreciate any help.

• ###### 1. Re: Using a calculated field in a formula

({SUM(If Year([Date])=2017 and (Month([Date])<=6)THEN [Revenue] END)}/{SUM(If Year([Date])=2017 and (Month([Date])<=6)THEN [Unit] END)})

*

(({SUM(If Year([Date])=Year(TODAY())THEN [Visits] END)}))

1 of 1 people found this helpful
• ###### 2. Re: Using a calculated field in a formula

Thanks for the quick response Deepak. Your formula is almost correct but I'm getting a slight error. For 2018 each month seems to have the same value. I was hoping that the Visit number of a particular month gets multiplied by the Revenue/Unit (constant).

• ###### 3. Re: Using a calculated field in a formula

Your description was for year anyways

here it is:

({SUM(If Year([Date])=2017 and (Month([Date])<=6)THEN [Revenue] END)}/{SUM(If Year([Date])=2017 and (Month([Date])<=6)THEN [Unit] END)})

*

(({FIXED DATEPART('month', [Date]):SUM(If Year([Date])=Year(TODAY())THEN [Visits] END)}))

and here is view of result using denominator only

1 of 1 people found this helpful
• ###### 4. Re: Using a calculated field in a formula

Thank you so much. You are a genius. I had to slightly modify your work to include a Fixed along my preferred Dimension.

• ###### 5. Re: Using a calculated field in a formula