Cumulative Average of another measure(s)

[navafolk]

Hi pros,

I am facing issue that I would want to create measure of cumulative average of another measure.

The input table looks like:

Date	Item	Amount-base	Amount-added
01-May	LocA	100	11
01-May	LocB	105	10
01-May	LocC	95	9
02-May	LocA	107	11
02-May	LocB	99	9
02-May	LocC	90	9
03-May	LocA	102	11
03-May	LocB	100	11
03-May	LocC	97	9

I prepare some measures:

Total LocA = CALCULATE(sum('Table'[Amount-base])+Sum('Table'[Amount-added]),'Table'[Item]="LocA")

Total LocB = CALCULATE(sum('Table'[Amount-base])+Sum('Table'[Amount-added]),'Table'[Item]="LocB")

 and measure of gap between LocA and LocB:

Gap LocA-B = [Total LocA]-[Total LocB]

 I need a cumulative average (cumulative since beginning date in the input table to the calculating date). This is what I have tried, but it did not work.

AVG LocA-B = calculate(averagex('Table',[Gap LocA-B]),FILTER(ALLSELECTED('Table'),'Table'[Date]<=max('Table'[Date])))

 My desired result would be:

Please help me with this measure. Thank you, all.

[MFelix · Accepted solution]

Hi @navafolk ,

Try the following code:

AVG LocA-B = AVERAGEX(
			FILTER(
				ALLSELECTED('Table'[Date]),
				'Table'[Date] <= MAX('Table'[Date])
			),
			[Gap LocA-B]
		)

[DataInsights · Accepted solution]

@navafolk,

Try this measure using the WINDOW function:

AVG LocA-B =
VAR vTable =
    ALLSELECTED ( 'Table'[Date] )
VAR vResult =
    AVERAGEX (
        WINDOW ( 0, ABS, 0, REL, vTable, ORDERBY ( 'Table'[Date], ASC ) ),
        [Gap LocA-B]
    )
RETURN
    vResult

[navafolk]

Great thanks, @DataInsights.

WINDOW works well and is too new to me, will research for future usage.

[MFelix · Accepted solution]

Hi @navafolk ,

Try the following code:

AVG LocA-B = AVERAGEX(
			FILTER(
				ALLSELECTED('Table'[Date]),
				'Table'[Date] <= MAX('Table'[Date])
			),
			[Gap LocA-B]
		)

[navafolk]

Super! Just simply what I need, I was too much thinking of CALCULATE, :D.

Thank you @MFelix 
(*) just a note for myself, I does not work with 'Date Hierarchy' in visual.