I forgot to mention in the above question. the Fiscal year in the second table(table two ) is a calculated field created using the effective dates between dd-mm-yyyy and dd-mm-yyyy is considerd fiscal year.
So I am trying to join/edit relationship between the fiscal year from Table 1 and join to Table 2.
Hi Rohini - I would recommend that you try using the raw date fields in your analysis, and simply modifying the fiscal year period at the data source level. The first part of this online help details how you would do that: Fiscal Dates . Hopefully this simplifies things, but please respond if it does not fix the issue.
To set the fiscal year start month for a data source, follow these steps:
- Right-click (Control-click on Mac) the data source in the Data pane to open the Date Properties dialog box.
- Set the Fiscal year start field to the appropriate month.
For each date dimension, you set the fiscal year start-month separately. In the Data pane, right-click a date dimension (Ctrl-click on Mac), and select Default Properties > Fiscal Year Start.
I'm a little confused by what you're trying to achieve, so I'll try and summarize the problem:
You have two data sources that you can't seem to join properly on fiscal year, yet you need each of them to react to a fiscal year filter?
If that's the problem then you should be able to use a parameter as a filter.
- Create a parameter called 'Choose Fiscal Year', set the type to String
- In the box below, enter your available fiscal years and select ok
- Create a calculated field called fiscalYr = STR(Fiscal Year)..if your fiscal year is coming from an actual date, it would have to be STR(YEAR(Date))
- Drag that calculated field to filters
- In the 'Edit Filter' window, select 'Condition', select 'By Formula' and enter [fiscalYr] = [Choose Fiscal Year]
- Go down to the bottom left, right-click your parameter, and select 'Show parameter control'
- Select your other data source
- Repeat steps 3-6
- Go to your dashboard. Select one of your sheets. Click the down-arrow, select parameters, and select the parameter you just made.
That parameter should show up in your dashboard and should function just like a filter would.
Also, one of your sources has fiscal year as just a number, while the other data source has fiscal year preceded by 'FY'. You would need to make the fields identical in order for them to work via parameter control.
Thanks for the inputs. I tried adding the parameter but one of fiscal years in the worksheet comes from a table which already has fiscal year data. The second worksheet only has the effective dates which shows any change with an employees salary.
when I edited relationship with fiscal years in both worksheets, they tie as well, just doesn't pop up on the dashboard.
Thank you for your input.
The issue is not setting up the fiscal dates but actually getting one single filter on dashboard with filters coming from both sheets.
I'm a little confused without a workbook.
But you can still use the parameter with year as long as you have two similar columns in your two tables.
So for the table that's bringing in only a fiscal year column, you would just make a calculated field for year and tie that calculated field to your parameter.
Could you attach a workbook so we can see what's going on?
I was able to get the one single filter when i changed the format of the data , in both the sheets.
I converted the fiscal month from a text to the numbers using LTRIM and that could match the other column's data .I was able to get it solved.
Thank you again for your inputs .