Calculate YTD values and Life to Date value based on Year, Month selection

[sush]

Hi  All,

I got a question, I need to display the sum of the amount column based on the filter pane selection and restrict values on Filter Pane.

Filter pane has a Year, Month selection. I need to restrict to select only one value from the year and one value from the month.

Filter Pane: Fiscal Year, Period

I am using a pivot table. Just to clarify the below is the data/columns I have to calculate the totals

Fiscal Year

Month Name

Account Number

Amount

Based on this data, I need to calculate the below 2 values based on the filter selection. These values to be displayed in the Pivot table along with the above-mentioned columns.

1. Current Fiscal Year Amount - Calculate YTD values based on the filter selection.

2. Life to Date Total  Amount- Calculate Life to date values based on Year, Month selection in the filter pane. 

Please let me know how can I achieve this.

Thanks

[thi_pham]

I assume you use KPI charts, Month_Name and Fiscal Year are supposed to be single choice. Hope it helps.

1. If Month_Name is not filtered : sum all months of selected year 
if Month_Name is selected: sum selected month, year.

if(GetSelectedCount([Month Name]) <= 0, (sum({<[Fiscal Year]={"$(=max([Fiscal Year]))"}>} [Current Fiscal Year Amount]) ,
(sum({<[Fiscal Year]={"$(=max([Fiscal Year]))"},[Month Name]={"$(=max([Month Name]))"}>} [Current Fiscal Year Amount]) )

As Month_Name and Fiscal Year are supposed to be single choice, we can make it shorter like:

if(GetSelectedCount([Month Name]) <= 0, (sum([Current Fiscal Year Amount]) ,
(sum( [Current Fiscal Year Amount]) )

2. sum([All Years Amount Total])

[sush]

Thank you so much for your response. I am using a pivot table and I have updated the same in my initial question as well. 

Could you please review and provide me suggestions?

Also just FYI, I don't have the columns Current Fiscal Year Amount and All Years Amount Total. I need to calculate these columns based on the filter selection and the amount column I have.

[thi_pham]

what are dimensions in your outcome pivot table?

If you provide a sample of your input data and your output pivot table, in excel or image, mightbe I could help.

[sush]

Please find attached the sample data. 

[thi_pham]

Sorry, I don't understand how you want to calculate YTD Total and Life TD Total, in output Pivot table, column Account No seems redundant. 

Please explain more detail or attach an excel file with formula.

[sush]

I have updated the image screenshot and the sample file. Could you please review and let me know?