Find the Average Demand of each season across the Time Series

[THENNA_41]

Sample Data : 

Date	Actauls
Jan-21	80
Feb-21	70
Mar-21	80
Apr-21	90
May-21	113
Jun-21	110
Jul-21	100
Aug-21	88
Sep-21	85
Oct-21	77
Nov-21	75
Dec-21	82
Jan-22	85
Feb-22	85
Mar-22	93
Apr-22	95
May-22	125
Jun-22	115
Jul-22	102
Aug-22	102
Sep-22	90
Oct-22	78
Nov-22	72
Dec-22	78
Jan-23	105
Feb-23	85
Mar-23	82
Apr-23	115
May-23	131
Jun-23	120
Jul-23	113
Aug-23	110
Sep-23	95
Oct-23	85
Nov-23	83
Dec-23	80

calculation 

1) Divide the data based on the number of Seasons

12 Seasons - Jan to Dec in a particular year .  I  trying find the Find the Demand of each season across the Time Series

Like below  

Month	2021	2022	2023
Jan	80	85	105
Feb	70	85	85
Mar	80	93	82
Apr	90	95	115
May	113	125	131
Jun	110	115	120
Jul	100	102	113
Aug	88	102	110
Sep	85	90	95
Oct	77	78	85
Nov	75	72	83
Dec	82	78	80

I am trying to find the average based on month in all years 

Example jan 2021 and jan 2022 & jan 2023 values are following  80,85,105 average is for jan 2021 to 2023  its 90

	Month	2021	2022	2023		Average (2021 -23)
	Jan	80	85	105		90
	Feb	70	85	85		80
	Mar	80	93	82		85
	Apr	90	95	115		100
	May	113	125	131		123
	Jun	110	115	120		115
	Jul	100	102	113		105
	Aug	88	102	110		100
	Sep	85	90	95		90
	Oct	77	78	85		80
	Nov	75	72	83		77
	Dec	82	78	80		80

finally  Calculate Overall Average Demand for all seasons (Months) 

Month	2021	2022	2023		Average (2021 -23)	Overall Average of Seasons
Jan	80	85	105		90		94
Feb	70	85	85		80		94
Mar	80	93	82		85		94
Apr	90	95	115		100		94
May	113	125	131		123		94
Jun	110	115	120		115		94
Jul	100	102	113		105		94
Aug	88	102	110		100		94
Sep	85	90	95		90		94
Oct	77	78	85		80		94
Nov	75	72	83		77		94
Dec	82	78	80		80		94
Avg	88	93	100		94

How to achive this ? please help me  i want it existing table itself dont want new table .

[v-zhengdxu-msft]

Hi @THENNA_41 

Please allow me to provide another insight:

Here's the Table for test:

Table:

Then add a measure:

MEASURE =
IF (
    HASONEVALUE ( 'Table'[Date] ),
    SUM ( 'Table'[Actauls] ),
    AVERAGE ( 'Table'[Actauls] )
)

Finally create a matrix, the result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[THENNA_41]

@jgeddes  Thank you so much for your help.. also i want show 2024 seperatly  like screen shot                                  

Previous Data its 2021 - 2023. so i want create like above for 2024?

[jgeddes · Accepted solution]

See the attached pbix.

I changed the previous year average measure to only include 2021-2023. I added a 2024 measure and created a new matrix visual with the values switched to rows.

[THENNA_41]

@jgeddes  Thank you so much for your support ... now  applied above measure in report 

[jgeddes]

One approach is to create an individual measure for each situation. In this case there will be 5 measures.

Average 2021 - 2023 = 
AVERAGEX(
    'Table',
    'Table'[Actauls]
)

Average 2021 = 
AVERAGEX(
    FILTER('Table', 'Table'[Date].[Year] = 2021),
    'Table'[Actauls]
)

Change the year value in the above for the remaining two years.

Average of All Seasons = 
var _vTable = 
SUMMARIZE(
    'Table',
    'Table'[Date].[Month],
    "__value", CALCULATE(AVERAGE('Table'[Actauls]), ALL('Table'))
)
Return
AVERAGEX(_vTable, [__value])

Hope this gets you pointed in the right direction.

[THENNA_41]

Thank you so much for your support