Last Invoice Date and Value

[JEB85]

Hi

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

[TABLE="width: 203"]
<colgroup><col style="text-align: center;"><col span="2" style="text-align: center;"></colgroup><tbody>[TR]
[TD="align: center"]Date[/TD]
[TD="align: center"]Customer[/TD]
[TD="align: center"]Value[/TD]
[/TR]
[TR]
[TD="align: center"]01/01/2014[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]261[/TD]
[/TR]
[TR]
[TD="align: center"]01/01/2014[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]127[/TD]
[/TR]
[TR]
[TD="align: center"]01/01/2014[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]203[/TD]
[/TR]
[TR]
[TD="align: center"]01/02/2014[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]402[/TD]
[/TR]
[TR]
[TD="align: center"]01/02/2014[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]127[/TD]
[/TR]
[TR]
[TD="align: center"]01/02/2014[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]203[/TD]
[/TR]
[TR]
[TD="align: center"]01/02/2014[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]560[/TD]
[/TR]
[TR]
[TD="align: center"]01/03/2014[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]261[/TD]
[/TR]
[TR]
[TD="align: center"]01/03/2014[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]-261[/TD]
[/TR]
[TR]
[TD="align: center"]01/03/2014[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]203[/TD]
[/TR]
[TR]
[TD="align: center"]01/03/2014[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]400[/TD]
[/TR]
</tbody>[/TABLE]


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:

[TABLE="width: 206"]
<colgroup><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>[TR]
[TD="align: center"][/TD]
[TD="colspan: 2, align: center"]Measures[/TD]
[/TR]
[TR]
[TD="align: center"]Customer[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Value[/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]01/02/2014[/TD]
[TD="align: center"]402[/TD]
[/TR]
[TR]
[TD="align: center"]B[/TD]
[TD="align: center"]01/02/2014[/TD]
[TD="align: center"]127[/TD]
[/TR]
[TR]
[TD="align: center"]C[/TD]
[TD="align: center"]01/03/2014[/TD]
[TD="align: center"]203[/TD]
[/TR]
[TR]
[TD="align: center"]D[/TD]
[TD="align: center"]01/03/2014[/TD]
[TD="align: center"]400[/TD]
[/TR]
</tbody>[/TABLE]

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

<tbody>
[TD="align: center"]01/01/2014 00:00:00[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]261[/TD]
[TD="align: center"]261[/TD]

[TD="align: center"]01/01/2014 00:00:00[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]127[/TD]
[TD="align: center"]127[/TD]

[TD="align: center"]01/01/2014 00:00:00[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]203[/TD]
[TD="align: center"]203[/TD]

[TD="align: center"]01/02/2014 00:00:00[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]402[/TD]
[TD="align: center"]663[/TD]

[TD="align: center"]01/02/2014 00:00:00[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]127[/TD]
[TD="align: center"]254[/TD]

[TD="align: center"]01/02/2014 00:00:00[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]203[/TD]
[TD="align: center"]406[/TD]

[TD="align: center"]01/02/2014 00:00:00[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]560[/TD]
[TD="align: center"]560[/TD]

[TD="align: center"]01/03/2014 00:00:00[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]261[/TD]
[TD="align: center"]663[/TD]

[TD="align: center"]01/03/2014 00:00:00[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]-261[/TD]
[TD="align: center"]663[/TD]

[TD="align: center"]01/03/2014 00:00:00[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]203[/TD]
[TD="align: center"]609[/TD]

[TD="align: center"]01/03/2014 00:00:00[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]400[/TD]
[TD="align: center"]960[/TD]

</tbody>

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

Thanks