array functions in powerpivot

[maring28]

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...

[TABLE="width: 341"]
<tbody>[TR]
[TD]carID[/TD]
[TD]re-fueling date[/TD]
[TD]month[/TD]
[TD]desired outcome[/TD]
[/TR]
[TR]
[TD]1057875[/TD]
[TD]04/04/2013[/TD]
[TD]4[/TD]
[TD]28/04/2013[/TD]
[/TR]
[TR]
[TD]1057875[/TD]
[TD]07/04/2013[/TD]
[TD]4[/TD]
[TD]28/04/2013[/TD]
[/TR]
[TR]
[TD]1057875[/TD]
[TD]10/04/2013[/TD]
[TD]4[/TD]
[TD]28/04/2013[/TD]
[/TR]
[TR]
[TD]1057875[/TD]
[TD]14/04/2013[/TD]
[TD]4[/TD]
[TD]28/04/2013[/TD]
[/TR]
[TR]
[TD]1057875[/TD]
[TD]17/04/2013[/TD]
[TD]4[/TD]
[TD]28/04/2013[/TD]
[/TR]
[TR]
[TD]1057875[/TD]
[TD]21/04/2013[/TD]
[TD]4[/TD]
[TD]28/04/2013[/TD]
[/TR]
[TR]
[TD]1057875[/TD]
[TD]24/04/2013[/TD]
[TD]4[/TD]
[TD]28/04/2013[/TD]
[/TR]
[TR]
[TD]1057875[/TD]
[TD]28/04/2013[/TD]
[TD]4[/TD]
[TD]28/04/2013[/TD]
[/TR]
[TR]
[TD]1266273[/TD]
[TD]04/05/2013[/TD]
[TD]5[/TD]
[TD]27/05/2013[/TD]
[/TR]
[TR]
[TD]1266273[/TD]
[TD]11/05/2013[/TD]
[TD]5[/TD]
[TD]27/05/2013[/TD]
[/TR]
[TR]
[TD]1266273[/TD]
[TD]17/05/2013[/TD]
[TD]5[/TD]
[TD]27/05/2013[/TD]
[/TR]
[TR]
[TD]1266273[/TD]
[TD]27/05/2013[/TD]
[TD]5[/TD]
[TD]27/05/2013[/TD]
[/TR]
</tbody>[/TABLE]

Thanks very much!!
Marina

 

[julianwi]

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]

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

 

[maring28]

Thank you!
It works now