PowerPivot- um Latest File but with additional criterions

[AmitM]

Hi all,
I want to know how can I sum/display results not only by the latest file, but also by site + project + topic ?
For instance:
1) 1/1/11 file, USA site, Project1, Topic B: TotalQty=10
2) 1/3/11 file, USA site, Project1, Topic A: TotalQty=12
3) 1/5/11 file, USA site, Project1, Topic B: TotalQty=7
4) 1/1/11 file, Mexico site, Project3, Topic E: TotalQty=100
I will want to display the data of (2) (3) (4) in my pivots.
*The reason I don't need to use (1) is because (3) has the same Topic+Project+Site and it is more recent than (1).
I thought of:
=CALCULATE(SUM([TotalQty]),FILTER(Table1,Table1[File Date]=MAX(Table1[File Date])))
but that won't differentiate between the different sites, topics and projects, so I assume additional filtering is needed:
=CALCULATE(SUM([TotalQty]),FILTER(Table1,Table1[File Date]=MAX(Table1[File Date])),________???_________)


Appreciate your help!

 

[Ozeroth]

Hi AmitM,
Here's one way of doing it:

=CALCULATE (
    SUM ( [TotalQty] ),
    CALCULATETABLE (
        LASTDATE ( Table1[File Date] ),
        ALL ( Table1[File Date] )
    ),
    VALUES ( Table1[File Date] )
)

If you create a pivot table with File Date, Project, Site and Topic with this measure, it will only show the quantity where the date is the latest date for that particular Project/Site/Topic combination.

• The CALCULATETABLE(LASTDATE...)) part finds the latest date for the current filter context, ignoring the current File Date filter.
• VALUES ( Table1[File Date] ) just gives you the date(s) from the current filter context.
• The intersection of these two will either be empty (if the current date isn't the latest one) or will contain just the latest date for the particular Project/Site/Topic (if that date is in the current filter context), in which case the CALCULATE(...) will give you the quantity on that latest date, otherwise it returns blank.