# Cumulative Average in Power Pivot

• ### Question

• Hello,

Currently, I have this cumulative DAX below :

```= CALCULATE(
SUM(tab[Value]),
FILTER(
ALL(tab[Date]),
tab[Date] <= MAX(tab[Date])
)
)```

Which represents this calculations :
January  : Value_Month1
February : (Value_Month2 + Value_Month1)
March    : (Value_Month3 + Value_Month2 + Value_Month1)
April    : (Value_Month4 + Value_Month3 + Value_Month2 + Value_Month1)
...

... it works great but now my boss wants this :
January  : Value_Month1
February : (Value_Month2 + Value_Month1) / 2
March    : (Value_Month3 + Value_Month2 + Value_Month1) / 3
April    : (Value_Month4 + Value_Month3 + Value_Month2 + Value_Month1) / 4
...

I would say it looks like an "Average Cumulative" (I don't know how to call it) but I don't know how to DAX it, could you help me with this new formula please ?

Thanks,

Wednesday, June 3, 2015 8:22 AM

• Hi guk92,

Following on from your approach you could add another Calculated Field that returns the cumulative months:

```=
CALCULATE(
COUNTROWS(tab),
FILTER(
ALL(tab[Date]),
tab[Date] <= MAX(tab[Date])
)
)```

And then simply divide your cumulative value Calculated Field by this new cumulative month count one:

`=DIVIDE([CumulativeValue], [CumulativeMonths])`

Regards,

Website: http://www.nimblelearn.com, Twitter: @nimblelearn

Wednesday, June 3, 2015 2:21 PM
• Hi guk92,

Assuming that you want the behaviour shown below in the 'CumulativeAverageValue2' Calculated Field in this image...

...You could create another Calculated Field with DAX similar to the following...

```CumulativeAverageValue2:=
IF(
HASONEVALUE(tab[Date]),
[CumulativeAverageValue],
[CumulativeValue]
)```

In the above DAX example, 'CumulativeAverageValue' refers to the Calculated Field that was suggested in my earlier post. The resulting behaviour does seem a bit unusual (when considering that the original aim was a cumulative monthly average).

Regards,

Website: http://www.nimblelearn.com, Twitter: @nimblelearn

Saturday, June 20, 2015 2:41 PM

### All replies

• Does Date column contain every dates in a month or just month dates?

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog

Wednesday, June 3, 2015 8:50 AM
• tab[Date] is DateTime type, but only contains the first day of each months and years with this format DAY/MONTH/YEAR. Example :

01/01/2014

01/02/2014

01/03/2014

01/04/2014

01/05/2014

...

Wednesday, June 3, 2015 9:04 AM
• Hi guk92,

Following on from your approach you could add another Calculated Field that returns the cumulative months:

```=
CALCULATE(
COUNTROWS(tab),
FILTER(
ALL(tab[Date]),
tab[Date] <= MAX(tab[Date])
)
)```

And then simply divide your cumulative value Calculated Field by this new cumulative month count one:

`=DIVIDE([CumulativeValue], [CumulativeMonths])`

Regards,

Website: http://www.nimblelearn.com, Twitter: @nimblelearn

Wednesday, June 3, 2015 2:21 PM
• Hi,

Thank you for your answer Michael Amadi, this is the formula I was looking for but I have a problem with it.

When I use your formula the elements are good, but it create an implicit average for the elements like this :

• PowerPivot shows me : 4.26 (Average) for 1.24 | 7.04 | 6.55 | 3.64 | 4.04 | 3.04
• But I want this : 25.55 (Sum) for 1.24 | 7.04 | 6.55 | 3.64 | 4.04 | 3.04

Thanks

Tuesday, June 9, 2015 1:15 PM
• Hi guk92,

Is there some sample data that you can provide or mock-up along with the expected output? Providing this will make it easier to refine the DAX formula to give the expected behaviour.

Regards,

Website: http://www.nimblelearn.com, Twitter: @nimblelearn

Wednesday, June 10, 2015 11:50 PM
• Can you also post the measures you are using. What Michael posted should work if you use something like
MaxMonth:=FORMAT(MAX(tab[Date]),"mm") for your month division value
Thursday, June 11, 2015 3:32 AM
• Hi guk92,

Assuming that you want the behaviour shown below in the 'CumulativeAverageValue2' Calculated Field in this image...

...You could create another Calculated Field with DAX similar to the following...

```CumulativeAverageValue2:=
IF(
HASONEVALUE(tab[Date]),
[CumulativeAverageValue],
[CumulativeValue]
)```

In the above DAX example, 'CumulativeAverageValue' refers to the Calculated Field that was suggested in my earlier post. The resulting behaviour does seem a bit unusual (when considering that the original aim was a cumulative monthly average).

Regards,