Last Invoice Date and Value

[JEB85]

Hi

My powerpivot data, simplistically, is set up like so:

Date	Customer	Value
01/01/2014	A	261
01/01/2014	B	127
01/01/2014	C	203
01/02/2014	A	402
01/02/2014	B	127
01/02/2014	C	203
01/02/2014	D	560
01/03/2014	A	261
01/03/2014	A	-261
01/03/2014	C	203
01/03/2014	D	400

<colgroup><col style="text-align: center;"><col span="2" style="text-align: center;"></colgroup><tbody>
</tbody>

My goal is to:
Find the date the customer was last invoiced
Where the net invoice value is greater than 0
Also bring back the associated value

The desired result would be as follows:

	Measures
Customer	Date	Value
A	01/02/2014	402
B	01/02/2014	127
C	01/03/2014	203
D	01/03/2014	400

<colgroup><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>
</tbody>

Is this possible? Thanks

 

[scottsen]

likely that LASTNONBLANK is going to help you here. It returns the max value in the first param (your date), where some expression is not BLANK, so you need to write that 2nd param to return blanks as appropriate.

Easiest if you precalc the Net Invoice in a calc column:
=LASTNONBLANK(MyTable[Date], MyTable[Net Invoice] > 0)

But also doable in a measure, just trickier

 

[JEB85]

Thanks. I'll give it a go tomorrow and let you know how it goes..

 

[JEB85]

Hi Scottsen,

How do you create the net invoice calc column?

I know how to do this in regular excel but not sure how to do it in powerpivot.

Thanks

 

[scottsen]

Ya, kinda threw you do the wolves on that

I think something like this would work:
=CALCULATE(SUM(MyTable[Value]), ALL(MyTable), MyTable[Customer] = EARLIER(MyTable[Customer]), MyTable[Date] <= EARLIER(MyTable[Date]))

And to understand that madness, try looking at this: The DAX EARLIER Function | Power Pivot | Tiny Lizard

 

[JEB85]

Hi,

I've tried that formula and it yielded the following:

=CALCULATE(SUM(Table1[Value]),ALL(Table1),Table1[Customer]=EARLIER(Table1[Customer]),Table1[Date]<=EARLIER(Table1[Date]))

Date	Customer	Value	NetInvoiceTest
01/01/2014 00:00:00	A	261	261
01/01/2014 00:00:00	B	127	127
01/01/2014 00:00:00	C	203	203
01/02/2014 00:00:00	A	402	663
01/02/2014 00:00:00	B	127	254
01/02/2014 00:00:00	C	203	406
01/02/2014 00:00:00	D	560	560
01/03/2014 00:00:00	A	261	663
01/03/2014 00:00:00	A	-261	663
01/03/2014 00:00:00	C	203	609
01/03/2014 00:00:00	D	400	960

<tbody>
</tbody>

So it looks like it's doing a cumulative calculation?

Thanks