EARLIER against a measure

[Anonymous]

Hi,

I’ve got a measure (named Output) which is a SUMX formula with some calculations against the Number columns.

Now I want to have an AVG (and later a MIN and MAX) of some values from the Output and the criteria is the ID1 column.

Example: I’ve got two values in ID1 with 6 so the AVG of the two output values is 1360.79 and I’ve got four values with 14 so the AVG of the four output values is 1443.68

Normally I’ll go for the EARLIER formula, but that won’t work against a measure (error because Output isn’t a column but a measure)

Any ideas? I’ve put some printscreens in the post

Thnx,

Manfred

[tringuyenminh92]

Hi @Anonymous,

Earlier and earliest methods are used in Calculated Column. Please check again and understand that context

[Anonymous]

Hi,

Yes I know that. I want to create a calculated column but I need data from a measure in combination with EARLIER. I can't change the measure to a calculated column because I need to use SUMX. I need to use SUMX instead of SUM because I want to do some calculations against values in some columns.

I can't find the solution to mix them (I'am a newbee)

EDIT:

Found a workarround by first calculate the AVG of some columns (create calculated colums) and then doing the SUMX (measure). I've create a printscreen of it. Maybe someone got a better idea

[v-huizhn-msft · Accepted solution]

Hi @Anonymous,

You’d better create a calculated column rather than measure. You can get the same result without SUMX function. I try reproduce your scenario and get the expected result.

First, try to create a calculated column using the following formula. Then calculate the average based on new calculated column.

Output = Table1[Number1]*0.75+Table1[Number2]*0.2+Table1[Number3]*0.125+Table1[Number4]*0.2+Table1[Number5]*0.75

Ave = CALCULATE(AVERAGE(Table1[Output]),FILTER(Table1,Table1[ID1]=EARLIER(Table1[ID1])))

If you have any question, please feel free to ask.

Best Regards,
Aneglia

[Anonymous]

Yesterday I've created this one (instead of the SUMX measure)

Output = ([Number1] * 0.75) + ([Number2] * 0.2) + ([Number3] * 0.125) + ([Number4] * 0.2) + ([Number5] * 0.75)

Then I can do the 

AVG = CALCULATE (
	AVERAGE(RAW[Output]),
	FILTER(RAW,RAW[ID1]=EARLIER(RAW[ID1])))

Works so far 🙂

EDIT:

I see that we almost do the same, only you use (like it should be) the table name in the calculated column. The other code I already received from you 

[tringuyenminh92]

Yeah @Anonymous. Haha, I have same workaround solution like you when doing fluktuation (%) in 12 months and YTD fluktuation (= total of percentage of 12 months).