3 Replies Latest reply on Oct 9, 2018 9:20 PM by Deepak Rai

# Straight-line calculation...possible?

Hi there, Fairly new to Tableau and was hoping that someone can assist or direct me to the right solution.  Note that I usually do this in excel, but my hope it to do it within Tableau going forward.  Here is my challenge...

My source data for budget entries comes in 3 columns (red text): Start Date, End Date, Budget Amount.  For example, start date=2019, end date=2024, budget amount=\$100,000.00.  In excel I divide this amount by the number of years and create a set of new columns as shown below.  Can this be accomplished in Tableau? Note that as shown below, some other rows will have different data ranges and amounts.  I am looking for an automated way to calculate the spend and for tableau to create the additional year columns.  I am open to other ways to accomplish this.  My main goal is to estimate/forecast total amount to be spend in a given year for all projects (i.e., 2022, 2023 etc.) . Thank you...

Start DateEnd DateBudget Amount 20192020
2022202320242025
Jan 1, 2019Jan 1, 2024\$100,000.00\$10,000\$10,000\$10,000\$10,000\$10,000
Jan 1, 2019Jan 1, 2025\$120,000.00\$20,000\$20,000\$20,000\$20,000\$20,000\$20,000
• ###### 1. Re: Straight-line calculation...possible?

Joshua- Do you need to have those columns for additional years when all you're doing is to divide the "Budget Amount" equally? Just create a calc "Budget by Year" and say,

SUM(Budget Amount)/SUM(DATEDIFF('year', startdate, enddate))

or if this isn't the case let us know.

Best,

Shiva.

• ###### 2. Re: Straight-line calculation...possible?

Hi Shiva, Thanks.  I do need the extra year columns to be populated, so the suggestion you made takes care of part of my challenge.

• ###### 3. Re: Straight-line calculation...possible?

You may look at this Reference by kettan. Definitely your Answer is there.

Thanks

Deepak