3 Replies Latest reply on May 7, 2018 4:31 AM by Okechukwu Ossai

# MTD

Hi All,

I have sales data may 1st to may 31st but i want TO calculate MTD that means may 1st to may 7th how can I calculate I have to use below formula

MTD:

sum(IF [ActualDate] <= TODAY() and

DATEDIFF('month',[ActualDate],TODAY())=0 THEN [Sales] else 1 END)

is it correct or not.

Thanks,

Veeru

• ###### 1. Re: MTD

Yes, This calculation is correct. And if you want to use YTD then just use Year instead of Month.

Thanks

Suman

• ###### 2. Re: MTD

Hello Chowdary,

The calculation is correct only.

IF [Date] <= TODAY() AND DATEDIFF('month',[Date],TODAY())=0 THEN [Sales] END

What exactly the issue you are facing?

My observation: I've data in excel like below:

 Date Sales 15-Apr 1 16-Apr 2 17-Apr 3 18-Apr 4 19-Apr 5 20-Apr 6 21-Apr 7 22-Apr 8 23-Apr 9 24-Apr 10

When double click on the Date cell in excel, it will show the year as 2018. When I copy the same to the clipboard and paste in Tableau, Tableau considered year as 1900.

Hope you are also facing the same?

S

• ###### 3. Re: MTD

Hi Chowdary,

Remove the ' Else 1' clause in your formula or you can use the formula below.

Create calculated field [MTD Sales]

IF DATETRUNC('month', [ActualDate]) = DATETRUNC('month', TODAY()) AND [ActualDate] <= TODAY() THEN [Sales] END

Hope this helps.

Ossai