6 Replies Latest reply on Jun 11, 2018 1:12 PM by Samanth Yadav

# YTD Calculation

Hi All,

I'm stuck with YTD calculation. Attached is the excel file which has fields Loaddate, Date(a Parameter), Dollar, Volume, Dollar/Volume (Calculated in excel) and YTD (Calculated in excel).

My Calculation for YTD:

and CONTAINS([Metric],"Dollar") then [Value] end)/sum(if DATEPART('month',[LOADDT])<=datepart('month',[Date])

and CONTAINS([Metric],"Volume") then [Value] end)

This holds good for January. But when I select February it sums up the JAN and FEB dollar & volume and then gives the result.

For example, when I select Jan 17 the Dollar/Volume is 20 and when I select Feb 17  the YTD comes up as15 as per my calculation which is the not the result I'm looking for. I need the result to be JAN-dollar/vol + FEB-dollar/vol

(i.e. 20+13.33 = 33.33) similarly, when i select Mar 17 I need the result to be JAN-dollar/vol + FEB-dollar/vol + + Mar-dollar/vol(i.e. 20+13.33+12 = 45.33) .

Can someone please help me out with the right calculation.

Regards,

Sam

• ###### 2. Re: YTD Calculation

Hi Shin,

Thanks for the response.

But from database I do get only dollar and volume value for each month. I provided the Dollar/volume and YTD column and its values for reference.

So here is what I'm looking for. I have a date (Parameter) as a filter and when I select a particular month I need to see only a single row of data just with the YTD value for the month selected. So when I select FEB 2017 I need to display as 33, When MAR 2017 is selected the value to be displayed is 45 as so on for the other months.

Please let me know how the calculation can be written or modifying my calculation.

Thanks,

Sam

• ###### 3. Re: YTD Calculation

HI Samanth

The normal filter filters underlying data and cumulative calc is broken, so you need to take this approach.

Filtering the View Without Filtering Underlying Data | Tableau Software  If this helps, please mark my answer as correct to close the thread, not from inbox view but from original post.

Thanks,

Shin

• ###### 4. Re: YTD Calculation

Hi Shin,

Attached is the Sample Data Workbook.

If you look into the workbook. I'm able to get the Year over Year Comparison (MTD Volume, MTD Change, MTD % Change) right but when it comes to Year to Date Comparison (YTD Volume, YTD Change, YTD % Change) I don't get the exact value because of the calculation. I need to use the date parameter as I'm working on multiple metrics and RDL is one of them where I am stuck.

Your earlier solution doesn't include any calculation which uses the date parameter. I need to present a single row of data based on the date (parameter) selected. Can you please look into my calculation for YTD Volume and YTD change and help me out so that when I select FEB 2018 the YTD Volume will be 895 similarly when selected MAR 2018 YTD Volume  = 1223.

Thanks,

Samanth

• ###### 6. Re: YTD Calculation

Thanks a lot Shin .