4 Replies Latest reply on Apr 28, 2017 12:14 AM by Norbert Maijoor

# Need to exclude incomplete current month actuals on a Dual Axis Line graph

I have a dual axis line graph with Actuals (with a line representing current year and prior year) and current year budget.  The data is by month, so when a month is incomplete the line has a sloping effect that completely skews the graph.

What formula can i use to exclude incomplete month/most current month?

I've searched and searched but can't seem to get a formula to help with this particular instance.

Thanks in advance!!

• ###### 1. Re: Need to exclude incomplete current month actuals on a Dual Axis Line graph

Hi Will

- I don't have a data set to test this against but I think this will do what you want

Last Month > DATEADD('month',-1,DATETRUNC('month',(TODAY())))

then you can use it in If or Case statements as you need to

Let me know if this helps

Jim

• ###### 2. Re: Need to exclude incomplete current month actuals on a Dual Axis Line graph

Hi Will,

Find my approach as reference below and stored in attached workbook version 9.3 located in the original thread.

1. Current Month-1: if datediff('month',[date],today())>1 then [actual] END

Regards,

Norbert

• ###### 3. Re: Need to exclude incomplete current month actuals on a Dual Axis Line graph

I appreciate the help, but I don't think it exactly meets the needs I'm looking for.  Please see the attached sheet

• ###### 4. Re: Need to exclude incomplete current month actuals on a Dual Axis Line graph

Hi Will,

Find my updated approach as reference below and stored in attached workbook version 10.2 located in the original thread

1. 2016 Actual: if [year]=2016 then [Actual] END

2. 2017 Budget: if [year]=2017 then [Budget] END

3. 2017 Actual: if [year]=2017 and datediff('month',[Date],today())>1 then [Actual] END

4. 2016 Workdays: if [year]=2016 then [Work Days] END

5. 2017 Workdays: if [year]=2017 then [Work Days] END

7. avg 2016 Actual: sum([2016 actual])/sum([2016 workdays])

8. avg 2017 Budget: sum([2017 budget])/sum([2017 workdays])

9. avg 2017 Actual: sum([2017 actual])/sum([2017 workdays])

Regards,

Norbert