topic Find the Average Demand of each season across the Time Series in DAX Commands and Tips

Find the Average Demand of each season across the Time Series

Anonymous — Wed, 25 Sep 2024 13:29:22 GMT

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 .

Re: Find the Average Demand of each season across the Time Series

jgeddes — Wed, 25 Sep 2024 14:39:13 GMT

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.

Re: Find the Average Demand of each season across the Time Series

Anonymous — Wed, 25 Sep 2024 18:26:39 GMT

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?

Re: Find the Average Demand of each season across the Time Series

jgeddes — Wed, 25 Sep 2024 18:42:22 GMT

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.

Re: Find the Average Demand of each season across the Time Series

Anonymous — Thu, 26 Sep 2024 02:46:48 GMT

Hi Anonymous

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.

Re: Find the Average Demand of each season across the Time Series

Anonymous — Thu, 26 Sep 2024 05:25:16 GMT

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

Re: Find the Average Demand of each season across the Time Series

Anonymous — Thu, 26 Sep 2024 05:26:00 GMT

Thank you so much for your support