topic Re: Calculate sum of all prior years based on Year, Month selection in App Development

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

sush — Sat, 16 Nov 2024 18:27:27 GMT

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

Re: Calculate sum of all prior years based on Year, Month selection

thi_pham — Tue, 23 Jun 2020 11:35:05 GMT

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])

Re: Calculate sum of all prior years based on Year, Month selection

sush — Tue, 23 Jun 2020 15:43:28 GMT

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.

Re: Calculate sum of all prior years based on Year, Month selection

thi_pham — Thu, 25 Jun 2020 08:42:41 GMT

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.

Re: Calculate sum of all prior years based on Year, Month selection

sush — Fri, 26 Jun 2020 15:21:12 GMT

Please find attached the sample data.

Re: Calculate sum of all prior years based on Year, Month selection

thi_pham — Fri, 26 Jun 2020 08:06:10 GMT

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.

Re: Calculate sum of all prior years based on Year, Month selection

sush — Fri, 26 Jun 2020 15:22:05 GMT

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