Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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]) )
)
Solved! Go to 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
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
Thanks a lot!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
23 | |
21 | |
19 | |
13 | |
12 |
User | Count |
---|---|
43 | |
31 | |
24 | |
22 | |
22 |