Access previous record fields

[paivers]

I would like to have a calculated field use data from a previous row. An example is I want to get the payment method from the most recent customer sale. I know how to use maxx with a filtered set for this customer, to get the max date prior to this sale. But then I want to get the payment method from that previous sale. I am guessing I need to somehow get the previous sales ID, and use that to access that row to get the field. Any ideas how to approach this?

 

[miguel.escobar]

you'd need to use CALCULATE and then FILTER() using a column that somehow has some sort of sequence (numerical) so you can go forward or trying to get some sort of running total. It's hard to give you an exact formula but that's basically what I'd do in order to get the right context/filter.

 

[paivers]

I could calculate/Filter is I had the previous key. The issue is that the only way to determine the most recent sale is using maxx(), but that just returns one value, the max date. I need a way to get that records identifier so that I can access it.

 

[CharlesChuckieCharles]

you should check out the the Domain commands

something like

DGET(table, field, criteria) where the criteria is your MAX command

but i don't recall it being DGET ( which is excel) but i don't have Access to Access at the moment

 

[paivers]

DGET sounds ideal, but unfortunately this is PowerPivot with DAX not Access. I don't see a DAX DGET formula, but an equivalent would work if anyone knows one.

 

[miguel.escobar]

do you think that you could upload a copy of your workbook so I can take a look at it? can't visualize your pivot table nor data model right now

 

[paivers]

do you think that you could upload a copy of your workbook so I can take a look at it? can't visualize your pivot table nor data model right now

Miguel,
Here’s an example using bicycle sales. I am trying to calculate the last field, Previous Type. To do that I filter by CustID to get the max date prior to the current, which gives row 1. From there I don’t know how to access the Type field of row 1. I suspect I need the SalesID, but it’s the same issue getting that.

SalesID		CustID		SalesDate		Type		Previous Type
1		1		1/3/2013		Hybrid
2		2		2/7/2013		Mountain
3		1		5/2/2013		Road		Hybrid

<tbody>
</tbody>

 

[miguel.escobar]

Paivers,

My apologies. I won't have time to look at anything during the next few weeks (busy days for business).
But I can tell you that you could use EARLIER in order to accomplish what you want.

Best of luck!

 

[paivers]

Miguel,
Thanks, hopefully someone else can help. I do use EARLIER in the filter for compares, but I don’t see how that can be used outside of the filter context. My formula looks like this.


=MAXX(
FILTER('Sales',(
'Sales'[CustID] = EARLIER('Sales'[CustID]))
'Sales'[SalesDate] < EARLIER('Sales'[SalesDate]))
,'Sales'[SalesDate]
)

That returns the most recent SalesDate, now I need to find the Type on that date.

 

[miguel.escobar]

try something more like CALCULATE( MAX( your column goes here), FILTER ( the table, the EARLIER part here) )