Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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's avatar
    moumipanja
    Icon for Microsoft Employee rankMicrosoft 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.