Average calculation

[Anonymous]

I have the following table:

I would like to create a new column called average that does the average calculation as follows:

1. The yellow highlight has Oct 2018 and Nov 2018. So Nov 2018 Avereage value should be Value in nov + Val in oct/2. Similarly, Oct should be value in oct+val in Sept/2.

Basically for a given month, the avaerage is: Value in that month + Value in previous month/2.

Is there a way this can be done dynamically?

Thanks!

[dedelman_clng · Accepted solution]

var __Val = CALCULATE( (SUM( Table[Value] ) )
var __ValLM = CALCULATE (SUM (Table[Value] ) , PREVIOUSMONTH( Date[Date] ) )

return
DIVIDE( __Val + __ValLM, 2) 

[dedelman_clng]

If your measure is [Value] and you have a calendar table associated with your month/year column, this measure should work:

Rolling 2 Avg = DIVIDE( [Value] + CALCULATE( [Value], PREVIOUSMONTH(Date[Date]) ), 2) 

Hope this helps,

David

[Anonymous]

@dedelman_clng Hey Value is not a measure, but a column. It is already there in the data set.

[dedelman_clng · Accepted solution]

var __Val = CALCULATE( (SUM( Table[Value] ) )
var __ValLM = CALCULATE (SUM (Table[Value] ) , PREVIOUSMONTH( Date[Date] ) )

return
DIVIDE( __Val + __ValLM, 2) 

[Anonymous]

@dedelman_clng I have to calculate this only for a metric called " Headcount". Is is a measure?

Also when I start typing, it throws an error:

[dedelman_clng]

Yes, it should be a measure.

The code should start

Measure Name = var __Value ...

[Anonymous]

@dedelman_clng Hey that worked. Just a quick doubt. I have data starting from Jan 2017 - Dec 2018. So for Jan 2017 I would like it to be the same value. But that also shows the average (Quite confused because it does not have dec 2016 data). Anything that can be done?

Thanks!

[dedelman_clng · Accepted solution]

Make your return statement something like this. It will return just the month value if there is no value from the previous month:

return
IF (ISBLANK( __ValLM ), __Val, DIVIDE( __Val + __ValLM, 2 ) )

[Anonymous]

@dedelman_clng Thanks!