Calculated field that only takes into account most recent data

[Juan Sombrero]

Hi all,

here's my (simplified) scenario

[TABLE="width: 312"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Shop[/TD]
[TD]Sale amount[/TD]
[/TR]
[TR]
[TD="align: right"]1/02/2013[/TD]
[TD]A[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]1/02/2013[/TD]
[TD]B[/TD]
[TD="align: right"]110[/TD]
[/TR]
[TR]
[TD="align: right"]1/02/2013[/TD]
[TD]C[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD="align: right"]1/02/2013[/TD]
[TD]D[/TD]
[TD="align: right"]130[/TD]
[/TR]
[TR]
[TD="align: right"]2/02/2013[/TD]
[TD]A[/TD]
[TD="align: right"]140[/TD]
[/TR]
[TR]
[TD="align: right"]2/02/2013[/TD]
[TD]B[/TD]
[TD="align: right"]150[/TD]
[/TR]
[TR]
[TD="align: right"]2/02/2013[/TD]
[TD]C[/TD]
[TD="align: right"]160[/TD]
[/TR]
[TR]
[TD="align: right"]2/02/2013[/TD]
[TD]D[/TD]
[TD="align: right"]170[/TD]
[/TR]
[TR]
[TD="align: right"]5/02/2013[/TD]
[TD]A[/TD]
[TD="align: right"]180[/TD]
[/TR]
[TR]
[TD="align: right"]5/02/2013[/TD]
[TD]B[/TD]
[TD="align: right"]190[/TD]
[/TR]
[TR]
[TD="align: right"]5/02/2013[/TD]
[TD]C[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD="align: right"]5/02/2013[/TD]
[TD]D[/TD]
[TD="align: right"]210[/TD]
[/TR]
[TR]
[TD="align: right"]6/02/2013[/TD]
[TD]A[/TD]
[TD="align: right"]220[/TD]
[/TR]
[TR]
[TD="align: right"]6/02/2013[/TD]
[TD]B[/TD]
[TD="align: right"]230[/TD]
[/TR]
[TR]
[TD="align: right"]6/02/2013[/TD]
[TD]C[/TD]
[TD="align: right"]240[/TD]
[/TR]
[TR]
[TD="align: right"]6/02/2013[/TD]
[TD]D[/TD]
[TD="align: right"]240[/TD]
[/TR]
</tbody>[/TABLE]

I have data from 4 of our shops, stating date + shop reference + sales amount.

Now I need a calculated field that only gives me sales amount of the last available day. So in this case the sum of all sales amount on 6/02/2013.

I tried "=CALCULATE(sum(salestable[sale amount]);LASTDATE(salestable[date])), assuming I would only get data from the latest date field...but this does not work. Using MAX in the filter field won't work either. And I cannot use the TODAY() function since I want this formula to work as well if there are no data dated today.

I'm convinced this can somehow be solved with applying the correct filter syntax in the calculate function.

Any suggestions?

Many thx
Juan

 

[bclaredal]

You could add a cell where you type in the date that you want to summarize and then have a sumif next to that date. It might look something like this assuming your new cell with the date you type in is in cell E1 and this formula is in cell F1 with your columns above being A, B and C:

=SUMIF($A:$A, $E:$1, "$C:$C)

If you wanted to extend that further you could use a SUMIFS and break it down by "Shop". In this case you would have to shift F1 above to G1 and add another entry item in cell F1 where you type in the "Shop" letter:

=SUMIFS($C:$C, $A:$A, $E:$1, $B:$B, $F:$1)

 

[Juan Sombrero]

Hi,

It's important I get this sorted out in PowerPivot (using a DAX function). I want to avoid any manual input in a reference cel. Formula should detect most recent date and only take sales data related to this date.

 

[bclaredal]

=sumif($a:$a,max($a:$a),$c:$c)