topic Re: 5-year average and year average % in 1 visual, seasonality in DAX Commands and Tips

5-year average and year average % in 1 visual, seasonality

pbi_expert — Wed, 22 Nov 2023 17:00:33 GMT

Hello Folks, FreemanZ Jihwan_Kim Dangar332 123abc

I have the daily data from 2018, and I would like to see the yearly seasonality in % with the 5-year average %.

x axis: months

y axis: %

Legend: probably years from date?: 2018, 2019, 2020, 2021, 2022, 2023 (this one until nov) + 5-year avg

I can make these work on separate visuals, but I can't integrate them into one. 5-year avg calc measure is included in the file. The desired visual format is also in the file.

Measure for 5-yr avg:

5Y_AVG_Corn = AVERAGEX ( SUMMARIZE ( FILTER ( 'Corn_Prices', YEAR ( 'Corn_Prices'[date] ) >= YEAR ( TODAY () ) - 5 && YEAR ( 'Corn_Prices'[date] ) < YEAR ( TODAY () ) ), 'Corn_Prices'[date].[Year] ), CALCULATE ( SUM ( 'Corn_Prices'[ value]) ) )

Data: corn_prices_pbiforum.xlsx

Visual mockup: (data of 2023 would finish in November)

Re: 5-year average and year average % in 1 visual, seasonality

pbi_expert — Wed, 22 Nov 2023 11:47:15 GMT

Unfortunately I could not add the data to the post, but I can send it if you need it!

Re: 5-year average and year average % in 1 visual, seasonality

Anonymous — Tue, 28 Nov 2023 03:14:24 GMT

Hi pbi_expert,

Perhaps you can try to use the following measure formula if it suitable for your requirement:

formula = VAR currDate = MAX ( 'Corn_Prices'[date] ) VAR summary = SUMMARIZE ( ADDCOLUMNS ( FILTER ( ALLSELECTED ( 'Corn_Prices' ), YEAR ( 'Corn_Prices'[date] ) >= YEAR ( currDate ) - 5 && YEAR ( 'Corn_Prices'[date] ) < YEAR ( currDate ) ), "Year", YEAR ( 'Corn_Prices'[date] ) ), [Year], "total", SUM ( 'Corn_Prices'[ value] ) ) RETURN AVERAGEX ( summary, [total] )

Regards,

Xiaoxin Sheng

Re: 5-year average and year average % in 1 visual, seasonality

pbi_expert — Mon, 04 Dec 2023 15:14:34 GMT

Thanks a lot!