I have a 3 fiscal year column sheet of actual revenue. I need to add a budget column at the end with a calculated over / under on budget. Is that possible? I can see creating another sheet and placing the two sheets side by side in a dashboard. But I would prefer adding the budget column and calculated column in the same sheet.
Doable?
Ken Adkins
Tableau
Excel
Hi Kenneth,
This is absolutely possible. For Budget O/U , you can use a calculated field. However, the budge column has to be present in your data so that we can calculate the Budget O/U. You need to bring in the FY17 YTD Budget column as it can't be derived from existing columns.
The calculation for the Budget O/U is simple
[FY17 YTD Actual ] - [FY 17 Budget ]
If the budget is available in other data source/excel workbook, you can still bring it in.
Happy Tableauing!!
Suhas
The Budget column is available in the data source measures. But when I add the column to Text, the column is displayed under Actual Revenue for each fiscal year. I only want the last fiscal year for budget, FY17. And I need the Budget column for FY17 right next to the Actual Revenue for FY17.
I'm not sure how to do this without creating two separate sheets and then placing those two sheets side-by-side in a dashboard.
Hi Kenneth,
Please follow the steps below.
1. Create a calculated field "Current Year Budget"
IF [Fiscal Year] = YEAR(TODAY()) THEN
[BUDGET]
END
2. Create a calculated field "Budget O/U"
IF [Fiscal Year] = YEAR(TODAY()) THEN
[REVENUE] - [Current Year Budget]
END
3. Put Measure values on the filter shelf and select only "Current Year Budget" and "Budget O/U".
4. Put Fiscal Year and Measure Names on the columns shelf
5. Put General Fund Revenue on Rows shelf
6. Put Measure Values on the Text Mark. If the text Mark is not available from Marks card, change the Mark Type to text.
Please note that this will still show you two columns for each Year. Howerver, there will be data only for current year.
If you need, you can Hide the label for the columns and on the dashboard use text boxes to put the headers.
Good Luck,
Suhas
Jan 12, 2017 11:24 PM
Morning Kenneth,
Find my approach as reference below and stored in attached workbook version 9.3
My assumption is that the datastructure is as follows
Define the following Measures
a. 2015 YTD Actual: if 2015=DATEPART('year',[Date]) then [Revenue] END
b. 2016 YTD Actual: if 2016=DATEPART('year',[Date]) then [Revenue] END
c. 2017 YTD Actual: if 2017=DATEPART('year',[Date]) then [Revenue] END
d. 2017 YTD Budget: if 2017=DATEPART('year',[Date]) and DATEPART(''month",[Date])<=DATEPART(''month",today())then [Budget] END
e. 2017 Budget O/U: [2017 YTD Actual ]-[2017 YTD Budget ]
Thanks to everyone for your feedback. And so quickly...
Each suggestion helped me learn a bit more. I'm getting close. I'm now trying to figure out how to eliminate hard coded values.
NORBERT MAIJOOR solution seems to work the best.
SUHAS DOKE solution opens up options to eliminate hard coded values. But I can't figure out how to adjust the columns and hide the columns I don't want to display in the sheet.
Using the following calculation I have to hard code the start fiscal year. This can then be used to calculate the prior fiscal years. The starting fiscal year can be a parameter I suppose. And you I have to give up the Fiscal Year column names. I suppose I can play with calculating those alias column names if that's possible.
calRevenueThisYear:
if 2017 =
if DATEPART("month",[AS_OF_DATE]) >= 7 then
DATEPART('year',[AS_OF_DATE]) + 1
else
DATEPART('year',[AS_OF_DATE])
end
then
[REVENUE]
else
0
end
Jan 13, 2017 12:34 PM
Hi Kenneth,
Glad I could help out. Thanks for the "Awards". Much appreciated:)
Find below my the "dynamic" version based on today()
a. Current YTD Actuals: if year(today())=DATEPART('year',[Date]) then [Revenue] END
b. Current -1 YTD Actuals: if year(today())-1=DATEPART('year',[Date]) then [Revenue] END
c. Current -2 YTD Actuals: if year(today())-2=DATEPART('year',[Date]) then [Revenue] END