Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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
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
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
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