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.

[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