I'm taking a look at this.
I am on a different version so I will walk you through the formula
First you need a calculated field for the actual fiscal date (the fiscal year option on the date field is for reporting and not for date math fromula)
sf the FY date is
IF MONTH([Date])>=10 then MAKEDATE(YEAR([Date])+1, MONTH([Date]),DAY([Date]))
else MAKEDATE(YEAR([Date]), MONTH([Date]),DAY([Date])) end
add a parameter for the fiscal year and month and Then create a filter for Fiscal Month MONTH([FY YTD])<= MONTH([enter FY year and month])
The result would look like this
Let me know if this helps
Sheet 2 is a copy of your sheet without the filters.
Sheet 3 has new stuff. I made a copy of your parameter. You can mix formats in a parameter. Mine is an integer parameter with character string display. your user sees "October", November", etc., but you have numbers to work with internally. I made a [fiscal month] calc that takes your date values and gives you a fiscal month integer value. (We use an October fiscal year here, so this is what we do all over the place in our workbooks.) Now you can filter against the parameter value using the fiscal month calc. See [limit fiscal months] that I used as a filter.
Sheet 4 gives you a single YTD value. Because the [highlight] is in the color shelf, it is breaking out April in the bars. I would take that off the sheet. Instead, I display the "as of" value in the title for the user.
Sample A.twbx 34.6 KB
Sorry Chris - my first response missed the mark - needed to a different filter for the FY YTD as shown below
MONTH([FY YTD])> MONTH([enter FY year and month]) and MONTH([FY YTD])<10 then drag the filter to the filter shelf and set to FALSE
and you will get this (note also I sorted the FY months (manually) to go Oct - through September
Thanks Joe, Sheet 4 is what I was going for. This is great!
By and large you are better off handling your dates as date fields, and your date parts as numeric values rather than strings. Just saying.
Since you have your date field set up to start fiscal year in October, Tableau will automatically display your years in fiscal year format and in fiscal year dimensions. If you put a filter on the sheet for YEAR([Date]), your users will see FY2014, FY2015, etc., as the filter choices, for example.
Is there a way to have the "Select Month" parameter filter, control the :"Select Month (copy)" parameter filter. I am hoping to limit it to just one filter on the dashboard to avoid confusion. So in other words the string parameter to also control the integer parameter?
I understand having everything set as date fields is ideal and to keep it as integer parameters but due to other calculated fields in the actual workbook (not included in the sample workbook), I could not do it that way and had to make months as string values.
Stick with your first parameter if it would be too disruptive to change over.
Instead, make a calc that translates the string parameter value to a number and use that calc when a number-driven calculation is more appropriate.
CASE [Select Month]
WHEN "October" then 1
When "November" then 2
When "September" then 12
We don't have a mechanism to change parameter values dynamically. (Not yet, anyway). So consider driving it this way.