11 Replies Latest reply on Jun 20, 2017 12:23 PM by aisha.elzein.0

# YTD and Prior YTD

Hi All,

Can you please help me out in calculating YTD and prior YTD calculations in the attached workbook.

Fiscal year FY2017 ranges from Apr-2016 to Mar-2017

Fiscal year FY2018 ranges from Apr-2017 to Mar-2018

If i select the date parameter as Feb-2018, YTD should be addition of actuals from Apr-2017 to Feb-2018 and Prior YTD should be addition of actuals from Apr-2016 to Feb-2017

Kindly let me out with the calculation.

David

• ###### 1. Re: YTD and Prior YTD

Hi David

I just helped out another user on this same question  see the attached

the formulas are Current year

IF [Date]>= DATETRUNC('month',DATEadd('month',-11,[enter year and month]))

then [Volumes] else 0 end

Prior year

IF [Date]>= DATETRUNC('month',DATEadd('month',-23,[enter year and month]))

then [Volumes] else 0 end

total current year

{ FIXED :sum(

IF [Date]>= DATETRUNC('month',DATEadd('month',-11,[enter year and month]))

then [Volumes] else 0 end )}

total prior year

{ FIXED  :sum(

if [Date]>= DATETRUNC('month',DATEadd('month',-23,[enter year and month]))

then [Volumes] else 0 end)}

Let me know if this helps

Jim

• ###### 2. Re: YTD and Prior YTD

Hi Jim,

Can you please save the above attached tableau workbook in an older version. I am not able to open it on my desktop.I am using Tableau 10.1.4.

Thanks,

David

• ###### 3. Re: YTD and Prior YTD

David see attached -

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 4. Re: YTD and Prior YTD

Hi Jim,

i applied the same logic in my workbook and it is not giving the correct calculations.

Can you please review my workbook and make changes, so that it works fine.

Many thanks for spending time to help me.

David

• ###### 5. Re: YTD and Prior YTD

david

I can't open the attachment - if you would attach an extracted workbook in twbx format I will take a look

Jim

• ###### 6. Re: YTD and Prior YTD

Sure Jim. Please find the attached file.

• ###### 7. Re: YTD and Prior YTD

see the tab marked Jim's solution

I made an adjustment to the formula to include the month/year of the parameter as the first month and then go back from there

The issue you were having was that you were using "Period " and year 1 instead of date in your viz - they are string fields not dates - the formula is doing date math and needs an actual date

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 8. Re: YTD and Prior YTD

Thank you Jim for taking time to provide solution. But the calculation you provided is calculating 12 months for CY and PY.

For example , If i select the date parameter as 2/25/2018, YTD should be addition of actuals from Apr-17 to Feb-18 and Prior YTD should be addition of actuals from Apr-16 to Feb-17

I have assigned date for each combination of period1 and year1. For example combination of Apr and FY2017 have a calculated field as 1/4/2016.

Please let me know if i am clear.

Thank you once again

David

• ###### 9. Re: YTD and Prior YTD

Hii David,

please find the attachment. I have done but not sure its right for you and not.

check it  and update me

Thanks & regards

Prashan Chaturvedi

1 of 1 people found this helpful
• ###### 10. Re: YTD and Prior YTD

Hi Prashant,

It worked for me. Thank you for your help.

David

• ###### 11. Re: YTD and Prior YTD

Hi Prashant,

I am looking for the same answer for this one, but unfortunately I cannot open the twbx workbook.