5 Replies Latest reply on Jun 12, 2019 9:12 PM by Zhouyi Zhang

# Excluding current financial year data from an average calculation

Hello All

I have a problem where I have a report looking a four years worth of financial year and year-to-date comparison data in a clustered bar chart. SO I need an average line for both the Year-to-date (YTD) and Financial Year (FY) data and the YTD is fine, but there is an obvious problem when averaging FY data because the current FY (starting in April) will skew the average terribly. Any ideas how I can limit the average to only apply to previous FY data?  I am using the following calculation for my average: {EXCLUDE [ReportYear] : AVG({FIXED [ReportYear] : iif(SUM([FinYr])=0,null,SUM([FinYr]))})}. My data looks like this:

And my graph looks like this - as you can see the average for FY should only be for completed Financial Years:

• ###### 1. Re: Excluding current financial year data from an average calculation

Hi, Andrew

Without seeing a sample workbook, I just copy your screenshot data as raw data and don't know how to determine whether a financial year is completed or not.

I just assume 19/20 is not completed, below is the screenshot of the calculation and its result for your reference.

A sample workbook attached.

hope this helps

ZZ

1 of 1 people found this helpful
• ###### 2. Re: Excluding current financial year data from an average calculation

Hi Zhouyi

Thank you for the reply. This would definitely work, but then it is hard coded to 2019/20. So I need to understand how to make a variable that would reflect the current financial year, that I could use instead of hard-coding the current financial year value.

Kind regards

Andrew

• ###### 3. Re: Excluding current financial year data from an average calculation

Hi, Andrew

I understand this, that's why I said without seeing your sample workbook, it is hard to determine that 2019/20 is the incompleted financial year, so if there is some flag or any other dimension can define what is a incompleted financial, then we can remove the hard code from the calculation.

ZZ

1 of 1 people found this helpful
• ###### 4. Re: Excluding current financial year data from an average calculation

Hi Zhouyi

You've just made me realise what I need to do.. The table I provide above is all the data there is. I did all the aggregation in SQL as it's easier to do it that way than to do it in Tableau when making FY and YTD comparisons. I will add a column that flags the current FY so I can exclude it from the average using the logic you provided.

Thank you very much

Andrew

• ###### 5. Re: Excluding current financial year data from an average calculation

No worries, hope everything goes well.

ZZ