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

[pbi_expert]

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)

[pbi_expert]

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

[v-shex-msft · Accepted solution]

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

[pbi_expert]

Thanks a lot!