cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resolver II
Resolver II

Re: previous 2 years average and show the data

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..

1.JPG

 

 

you will get your output.

1.JPG

View solution in original post

1 REPLY 1
Highlighted
Resolver II
Resolver II

Re: previous 2 years average and show the data

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..

1.JPG

 

 

you will get your output.

1.JPG

View solution in original post

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors