AVG of Calendar Years

[pbeeyeqs]

Each month there is a value. Trying to show the CALENDAR YEARLY AVERAGE by calculating, for example:

2018 YEARLY AVG = (DEC 2018 VALUE - DEC 2017 VALUE) / DEC 2017 VALUE

Then, have a table showing the average for each year:

2018 = 1.910%

2017 = 2.109%
...
sample data

DATE	VALUE
12/1/2018	251.233
11/1/2018	252.038
10/1/2018	252.885
9/1/2018	252.439
8/1/2018	252.146
7/1/2018	252.006
6/1/2018	251.989
5/1/2018	251.588
4/1/2018	250.546
3/1/2018	249.554
2/1/2018	248.991
1/1/2018	247.867
12/1/2017	246.524
11/1/2017	246.669
10/1/2017	246.663
9/1/2017	246.819
8/1/2017	245.519
7/1/2017	244.786
6/1/2017	244.955
5/1/2017	244.733
4/1/2017	244.524
3/1/2017	243.801
2/1/2017	243.603
1/1/2017	242.839
12/1/2016	241.432

[v-lid-msft · Accepted solution]

Hi @pbeeyeqs ,

We can create tables using following DAX to meet your requirement.

Table 2 =
VAR result =
    ADDCOLUMNS (
        SUMMARIZECOLUMNS ( 'Table'[DATE].[Year] ),
        "AVG", DIVIDE (
            LOOKUPVALUE ( 'Table'[VALUE], 'Table'[DATE], DATE ( [DATE].[Year], 12, 1 ) )
                - LOOKUPVALUE ( 'Table'[VALUE], 'Table'[DATE], DATE ( [DATE].[Year] - 1, 12, 1 ) ),
            LOOKUPVALUE ( 'Table'[VALUE], 'Table'[DATE], DATE ( [DATE].[Year] - 1, 12, 1 ) )
        )
    )
RETURN
    FILTER (
        result,
        [DATE].[Year] > MINX ( ALL ( 'Table' ), 'Table'[DATE].[Year] )
    )

BTW, pbix as attached.

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more 

[pbeeyeqs]

One more question:

Because the current year is not completed, it gives -100%. How would you force it to give YTD for the current year?

Thanks

[v-lid-msft]

Hi @pbeeyeqs ,

you can change the date value in LookupValue, if you have any further question, fell free to create a new post and describe your promble rather than reply in a answered question.

Thank you for your support.

Community Support Team _ DongLi