
1. Re: How to calculate Variance with prior 3 month average
Norbert Maijoor Feb 17, 2017 1:49 AM (in response to Naveen AM)Hi Naveem,
Find my approach as reference below and stored in attached workbook version 10.1 located in the original threat.
Step1 : Defined custom date Month/Year
Step2 : Defined first day of the month
First day of the month: {fixed [Date Num (Month / Year)]:min([Date Num])}
Step 3: Defined parameter based on calculated field First day of the month:
Step 4: Defined the values per month
Amount selected month: if DATEDIFF('month',[Date Num],[Month Selector])=0 then[Amount] END
Amount selected month 1: if DATEDIFF('month',[Date Num],[Month Selector])=1 then[Amount] END
Amount selected month 2: if DATEDIFF('month',[Date Num],[Month Selector])=2 then[Amount] END
Amount selected month 3: if DATEDIFF('month',[Date Num],[Month Selector])=2 then[Amount] END
Step 5: Defined final requested calculation
Variance with prior 3 months: sum([Amount selected month])(sum([Amount selected month 1])+sum([Amount selected month 2])+sum([Amount selected month 3]))/3/sum([Amount selected month])
Please validate. Your feedback is appreciated,
Regards,
Norbert

variance_calc.twbx 14.0 KB


2. Re: How to calculate Variance with prior 3 month average
Naveen AM Feb 17, 2017 3:54 AM (in response to Norbert Maijoor)Thanks for the response Norbert. However, the values i get from your workbook are not correct. To give a better idea of the calculation, I'm attaching the data file with a sample calculation. Thanks again for the help.

Mock data variance.xlsx 12.6 KB


3. Re: How to calculate Variance with prior 3 month average
Norbert Maijoor Feb 17, 2017 4:19 AM (in response to Naveen AM)
Updated 3months.twbx 85.2 KB


4. Re: How to calculate Variance with prior 3 month average
Naveen AM Feb 17, 2017 5:50 AM (in response to Norbert Maijoor)Thanks a lot Norbert.

5. Re: How to calculate Variance with prior 3 month average
Norbert Maijoor Feb 17, 2017 7:02 AM (in response to Naveen AM)HI Naveem,
You are welcome.

6. Re: How to calculate Variance with prior 3 month average
Teresa Wright Mar 8, 2018 12:19 PM (in response to Norbert Maijoor)Hello Norbert. I am also trying to create a worksheet displaying the current month plus three prior months. I found your posts and tried the calculations listed above but could not get them to work for me.
Screenshot 1: Custom Date Field
Screenshot 2: Month Selector Parameter (My data spans from 10/1/2017  1/31/2018). I want to view the data as monthly totals.
Screenshot 3: Error on first calculation. Is it something in my calculation? or my data?

Calculate Prior Months  2.twbx 619.2 KB


7. Re: How to calculate Variance with prior 3 month average
Norbert Maijoor Mar 9, 2018 12:27 PM (in response to Teresa Wright)