# array functions in powerpivot



## maring28 (Jul 24, 2013)

Hi,

Can I use array functions in powerpivot as I used to in excel?? and I'll explain:

I have a table: re-fueling dates, month(of re-fueling dates) and carID
I need to indicate the maximum date for each month and car.

Like in the table:
for the car 1057875, the last re-fueling date is 28/04/2013.
I used to use array functions for this calculation but in powerpivot I didn't find any suitable option to do so... 


carIDre-fueling datemonthdesired outcome105787504/04/2013428/04/2013105787507/04/2013428/04/2013105787510/04/2013428/04/2013105787514/04/2013428/04/2013105787517/04/2013428/04/2013105787521/04/2013428/04/2013105787524/04/2013428/04/2013105787528/04/2013428/04/2013126627304/05/2013527/05/2013126627311/05/2013527/05/2013126627317/05/2013527/05/2013126627327/05/2013527/05/2013

<tbody>

</tbody>
Thanks very much!! 
Marina


----------



## julianwi (Jul 24, 2013)

Should a car that is listed on multiple months always show the last date of that month? If so, you may try this as a calculated column:
=calculate(max(carrefuel[re-fueling-date]);
                           filter(
                                 all(carrefuel);
                                 carrefuel[car id]=earlier(carrefuel[car id]) &&
                                 carrefuel[month]=earlier(carrefuel[month])))


You may read about the earlier-function that is the key of this solution here: The Correct Usage of EARLIER() « PowerPivotPro


----------



## ethan12 (Jul 28, 2013)

AAhh thanks a lot i got my solution from it.i find this forum very helpful.Thanks


----------



## maring28 (Jul 28, 2013)

Thank you!
It works now


----------

