5 Replies Latest reply on Mar 10, 2016 10:14 PM by Tharashasank Davuluru

# Calculate fields for Current month Sum(amount), YTD Sum(Amount)

Hi All

I have a data set as follows

Period, Group, ServiceLine, SubServiceLine, Amount

'2016-01', A, AA1, AAA1, 1000.00

'2016-01', A1, AA2, AAA1, 1000.00

'2016-02', A3, AA3, AAA1, 1000.00

'2016-02', A4, AA4, AAA1, 1000.00

'2016-03', A5, AA5, AAA1, 1000.00

'2016-03', A, AA5, AAA1, 1000.00

I want to create a report to display  Group, ServiceLine, SubServiceLine,  Amount - CurrentMonth, Amount - YearToDate

How to create 2 calculated fields:  Amount(CurrentMonth), Amount(YearTodate)

Thanks

• ###### 1. Re: Calculate fields for Current month Sum(amount), YTD Sum(Amount)

HI ,

The period column  is in string format. can we change it to date. if we change it to date format your requirement is easy.

• ###### 2. Re: Calculate fields for Current month Sum(amount), YTD Sum(Amount)

That would be a problem. Lowest granularity of data is day level, so the filter should be a "Month".

• ###### 3. Re: Calculate fields for Current month Sum(amount), YTD Sum(Amount)

Hi ,

That's(Period)  not a date format . Tableau will consider it like a string only.

Actually what i am pointing , Is the period is date or not? if it is a date field it need to be written in date format or else it will be problem.

• ###### 4. Re: Calculate fields for Current month Sum(amount), YTD Sum(Amount)

At the moment its a String. But Its okey to convert it to a  date format.

• ###### 5. Re: Calculate fields for Current month Sum(amount), YTD Sum(Amount)

Hi Shanaka,

You this formula. formulate your period column into date filed then this will work.

YTD for previous year replace [Date] with your desired date, returns True if the date is in the YTD from the previous year

YEAR([Date]) == YEAR(TODAY())-1 AND (MONTH([Date]) < MONTH(TODAY()) OR (MONTH([Date]) == MONTH(TODAY()) AND DAY([Date]) <= DAY(TODAY())))