How to calculate the average for each month from the beginning of the year?

[MaximB]

Dear colleagues,

I can’t solve this problem, how to calculate the average from the beginning of the year for each month of this year and repeat this procedure for each next year?

For example I made a table in excel:

I try do it by DATESYTD, and with SUMX it works fine, but if I change SUMX in AVERAGEX, then I get a wrong values.

[Syndicate_Admin]

You can solve this by creating a measure with AVERAGEX on a filtered DATESYTD, but making sure that the calculation is done on a daily column and not on already aggregated monthly totals. For example:

PromedioYTD =
AVERAGEX(
    DATESYTD('Fecha'[Date]),
    [TuMedidaBase]
)

This will calculate the cumulative average since the start of the year for each month and will work correctly for different years as long as your date table is well marked as a calendar and contains all dates without gaps.

[gmsamborn]

Hi @MaximB 

Would something like this help?

Monthly Average = 
	AVERAGEX(
		FILTER(
			SUMMARIZE(
				'Date',
				'Date'[Year],
				'Date'[Month],
				"@Total", SUM( 'Outstanding'[outst] )
			),
			'Date'[Year] = SELECTEDVALUE( 'Date'[Year] )
		),
		[@Total]
	)

Monthly Averages.pbix

[MaximB]

Hi @gmsamborn ,
thanks for your time

I tried to use your, measure and got a not entirely correct result, or rather just the sum

Tried to work with your measure a little: 
Monthly Average = AVERAGEX( FILTER( SUMMARIZE( 'Data', 'Data'[Year], 'Data'[Month], "@Total", SUMX(Data,Data[Outstanding]) ), 'Data'[Year] = SELECTEDVALUE( 'Data'[Year] ) ), [@Total] )

[gmsamborn]

Hi @MaximB 

I think the main difference might be that you don't appear to be using a date table as your modified measure shows.

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[MaximB]

Hi @lbendlin ,
I prepared the expamle table with 4 different date:

Date	Product	Prov_type	Outstanding
30.11.2022	A	1	5
30.11.2022	B	1	10
30.11.2022	C	1	3
30.11.2022	A	2	4
30.11.2022	B	2	35
30.11.2022	C	2	62
30.11.2022	A	3	26
30.11.2022	B	3	23
30.11.2022	C	3	14
31.12.2022	A	1	12
31.12.2022	B	1	312
31.12.2022	C	1	3
31.12.2022	A	2	412
31.12.2022	B	2	35
31.12.2022	C	2	512
31.12.2022	A	3	865
31.12.2022	B	3	68
31.12.2022	C	3	568
31.01.2023	A	1	36
31.01.2023	B	1	10
31.01.2023	C	1	346
31.01.2023	A	2	466
31.01.2023	B	2	756
31.01.2023	C	2	385
31.01.2023	A	3	346
31.01.2023	B	3	23
31.01.2023	C	3	346
28.02.2023	A	1	5
28.02.2023	B	1	34
28.02.2023	C	1	63
28.02.2023	A	2	634
28.02.2023	B	2	35
28.02.2023	C	2	62
28.02.2023	A	3	96
28.02.2023	B	3	967
28.02.2023	C	3	678

And assume reuslt in the table under pivot:

[gmsamborn · Accepted solution]

Hi @MaximB 

It appears to me that you want an average based on a YTD total of monthly totals.
If that is the case, maybe try someting like this...

Monthly Average = 
VAR _Yr = SELECTEDVALUE( 'Date'[Year] )
VAR _Mon = SELECTEDVALUE( 'Date'[MonthNo] )
VAR _Table =
    FILTER(
        SUMMARIZE(
            ALL( 'Date' ),
            'Date'[Year],
            'Date'[MonthNo],
            "@Total", SUM( 'Outstanding'[Outstanding] )
        ),
        'Date'[Year] = _Yr
			&& 'Date'[MonthNo] <= _Mon
    )
VAR _Total =
	SUMX(
		_Table,
		[@Total]
	)
VAR _Count =
	COUNTAX(
		_Table,
		[MonthNo]
	)
VAR _Average =
	DIVIDE(
		_Total,
		_Count
	)
RETURN
	IF( NOT ISBLANK( SUM( 'Outstanding'[Outstanding] ) ), _Average )

Monthly Averages - try 2.pbix

[MaximB]

@gmsamborn , thank you very much, this is what I need. I transferred the formula to my real data and now everything works. Thank you again, I have been struggling with this problem for several days