Forum Discussion
previous 2 years average and show the data
Hi All,
Need help in solving the below . Need to calculate the average of previous year month .
Eg:
Date Regular Time
1/1/2017 0:00 55352.36
2/1/2017 0:00 48446.67
3/1/2017 0:00 59533.84
4/1/2017 0:00 47379.48
5/1/2017 0:00 59671.17
6/1/2017 0:00 53114.08
7/1/2017 0:00 45445.73
8/1/2017 0:00 52435.5
9/1/2017 0:00 49263.75
10/1/2017 0:00 56204.57
11/1/2017 0:00 55539.25
12/1/2017 0:00 40924.8
1/1/2018 0:00 53356.25
2/1/2018 0:00 41935.08
3/1/2018 0:00 51504.59
4/1/2018 0:00 50411.89
5/1/2018 0:00 56962
6/1/2018 0:00 49064.76
7/1/2018 0:00 46813
8/1/2018 0:00 48939.38
9/1/2018 0:00 46932.33
10/1/2018 0:00 30365.25
11/1/2018 0:00 2000
12/1/2018 0:00 112
Output will be (jan 2016+Jan2017)/2.
Here is the outlput.
Month Regular time
Jan 54354.305
Feb 45190.875
Mar 55519.215
Apr 48895.685
May 58316.585
Jun 51089.42
Jul 46129.365
Aug 50687.44
Sep 48098.04
Oct 43284.91
Nov 28769.625
dec 20518.4
You can use a matrix format to get the desired output. Use Date in Rows and keep only Month in the hierarchy. Use Regular Time in Values and then select Average in the Values field..
you will get your output.
1 Reply
- moumipanja
Microsoft Employee
You can use a matrix format to get the desired output. Use Date in Rows and keep only Month in the hierarchy. Use Regular Time in Values and then select Average in the Values field..
you will get your output.