I have monthly budget data for all departments and all account types for the entire fiscal year. Our fiscal year starts on July. My data contains 1 fiscal year worth data.
I am having problem querying correct YTD fiscal year budget figures using calculated field. I used the following formula:
DATEDIFF('year',[Effective Date],[Reference Date2] )=0
MONTH([Effective Date] )<= MONTH([Reference Date2] )
I made Reference Date2 parameter to show ranges of dates from July 1, 2013 to June 30, 2014 with 1 month increment.
I was able to show correct YTD figures from July 2013 to December 2013. Although I can only select 1 month (instead of multiple months), from July 2013 to December 2013, each month selected picked up YTD budget.
When I selected January 2014, YTD budget value picks up the sum of January 2014 budget only and drops July 2013 to Dec 2013 budget.
When I select June 2014, YTD budget is the sum of January 2014 budget through June 2014 budget, omitting July 2013 through December 2013 budget. When I add YTD budget as of June 2014 and that of December 2013, that figure is the correct annual budget.
How can I get correct YTD budget figure when I select January 2014 onwards?
Do I need to create a table to define our fiscal year first?
Any thought and advice is very much appreciated.
Could you provide a Tableau Packaged Workbook so we can see the issue in more detail? If your data is sensitive, you might mock up an example using Tableau's sample data.
In the mean time, are you using Tableau's Fiscal Date functionality?
It might get you what you're looking for.