Average of 12 Months column

[s2anya]

Following is the data sample we are dealing with , which is having 27 months data. From which we need to extract 12 months data and show it as defined in the output dataset. 

For now we are using matrix and have pivoted the data on date column, but we are not able to add the average column as per our requirement.

Data Sample-

Date	KPI-1	KPI-2	KPI-3	KPI-4	KPI-5	KPI-6
Saturday, October 01, 2016	691	585	501	654	565	586
Tuesday, November 01, 2016	575	516	636	562	648	513
Thursday, December 01, 2016	596	615	608	634	537	521
Sunday, January 01, 2017	512	600	699	698	572	589
Wednesday, February 01, 2017	534	520	691	538	614	551
Wednesday, March 01, 2017	520	531	663	551	561	540
Saturday, April 01, 2017	636	683	627	524	509	514
Monday, May 01, 2017	665	542	542	527	571	582
Thursday, June 01, 2017	639	517	679	632	559	631
Saturday, July 01, 2017	675	665	528	680	653	625
Tuesday, August 01, 2017	564	690	584	602	684	521
Friday, September 01, 2017	547	596	552	632	566	527
Thursday, October 01, 2015	537	641	603	680	528	687
Tuesday, November 01, 2016	594	631	694	518	644	628
Monday, December 01, 2014	699	527	699	504	502	520
Saturday, February 01, 2014	561	537	638	651	526	609
Tuesday, March 01, 2016	686	589	582	647	620	570
Friday, May 01, 2015	603	578	660	519	662	653

Output-

Metric	Oct-16	Nov-16	Dec-16	Jan-17	Feb-17	Mar-17	Apr-17	May-17	Jun-17	Jul-17	Aug-17	Sep-17	12 Month Avg
KPI-1	691	575	596	512	534	520	636	665	639	675	564	547	594.4166667
KPI-2	585	516	615	600	520	531	683	542	517	665	690	596	10367.33333
KPI-3	501	636	608	699	691	663	627	542	679	528	584	552	186.75
calculated KPI-4	654	562	634	698	538	551	524	527	632	680	602	632	594.4166667
calculated KPI-5	565	648	537	572	614	561	509	571	559	653	684	566	781.1666667
calculated KPI-6	95.5%	94.2%	94.1%	96.6%	94.6%	96.6%	95.1%	94.8%	94.7%	95.1%	95.8%	96.1%	95.3%

[Eric_Zhang · Accepted solution]

@s2anya

One way for your reference.

1.Unpivot the KPI columns.

2.Create a matrix with below measure, setting page level filter "is on or after" 2016-11-01.

Measure =
IF (
    HASONEVALUE ( yourTable[KPI] ) && HASONEVALUE ( yourTable[Date] ),
    SUM ( yourTable[Value] ),
    AVERAGE ( yourTable[Value] )
)

[s2anya]

it worked, Thankyou so much 🙂