Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
pbi_expert
New Member

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

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]) )
)

 

 
Visual mockup: (data of 2023 would finish in November)
seasonalvisual.JPG
1 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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

3 REPLIES 3
pbi_expert
New Member

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

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Thanks a lot!

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.