3 Replies Latest reply on May 19, 2013 9:01 PM by Lester Anciro

# Getting Month-to-Date sales using calculated field.

Hi guys,

I just want to ask if there is a way I can get my Month-to-Date sales using a calculated field?

I tried  this computation but it keeps on giving me ONE DAY sales.

`IF [Order Date] AND DATEDIFF('month',[Order Date],Today())= 0 THEN [Sales] END `

The 2.54M and 2.82M in above data must be more than 60M if it is month-to-date.

• ###### 1. Re: Getting Month-to-Date sales using calculated field.

Hi Lester,

DATEDIFF('month', start, end) is giving you the number of months between these dates. Your graph is a bit confusing. If the date on the x-axis is Order Date, I'm not sure why you'd have any values in the MTD calculation. All Order Dates should have a DATEDIFF > 0, given it's May.

But if you want to match the months, you could use MONTH([Order Date]) == MONTH(TODAY()). And for the month to date, you could do the same with DAY([Order Date]) <= DAY(TODAY()).

IF DAY([Order Date]) <= DAY(TODAY()) AND MONTH([Order Date]) == MONTH(TODAY()) THEN [Sales] END

But if you're already segmenting the view by month, as you have in your bar chart, you might just use

IF DAY([Order Date]) <= DAY(TODAY()) THEN [Sales] END

Jim

1 of 1 people found this helpful
• ###### 2. Re: Getting Month-to-Date sales using calculated field.

Hi Jim.

And sorry for the graph.

I'll explain it more clearly,

I have the data for month of March only, so I created a sample date with DATE = "3/26/13"

I want to get the Sales from March 1 up to the sample date March 26, how do I do it?

Thanks again.

Lester

• ###### 3. Re: Getting Month-to-Date sales using calculated field.

Got my answer to my question

Thanks for the help!