Calculate a total based on the most recent date for each unique value in a column

[erhodes]

I have a table with individual responses (name and score) grouped by a varibale (store) for a given date. What i need to do is write measure that allows me to calculate totals based on the most recent set of responses for each store. Below is an example of the table.

Name	Store	Date	Score
Joe	1	12/1/2016	5
Sue	1	12/1/2016	10
Mary	2	12/1/2016	1
Joe	1	1/1/2017	2
Bob	1	2/6/2017	5
Mary	2	2/10/2017	6
Jane	2	2/10/2017	6
Steve	3	2/15/2017	15

An example of the output i am looking for is:

Store	Most Recent Total Score	# of Names
1	7	2
2	12	2
3	15	1
total	34	5

Unfortunatly, i cant get the total correct. I'm using the following measure:

CALCULATE(
COUNTROWS(FactTable[Name],
FILTER(
VALUES(FactTable[Date]),FactTable[Date] = MAX(FactTable[Date)
))

When I build my table it shows me the correct total score and total # of names for each store, but the total shows 15 and 1 b/c that is the amount that corresponds the MAX date which is store 3.  Anyone know of a solution for this? 

[Phil_Seamark · Accepted solution]

or if you want it in a table in it's own right.  Create a new table using the following definition

FactTable2 = SUMMARIZE( FILTER(NATURALINNERJOIN(
                    'FactTable',
                    SUMMARIZECOLUMNS(
                        'FactTable'[Store],"Max Date" ,
                         MAX('FactTable'[Date])
                         )
                     ),[Max Date]=[Date]
                     ),
                     [Store] , 
                     "Most Recent Total Score" , SUM(FactTable[Score]), 
                     "# of Names" , COUNTROWS('FactTable')
                     )

[Sean]

@erhodes

Try these MEASURES...

Most Recent Total Score =
CALCULATE (
    SUM ( 'Table'[Score] ),
    FILTER ( 'Table', 'Table'[Date] = MAX ( 'Table'[Date] ) )
)

# Of Names =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Name] ),
    FILTER ( 'Table', 'Table'[Date] = MAX ( 'Table'[Date] ) )
)

Here's the result...

Hope this helps!

[erhodes]

thanks for the response but it didn't work for me. The total row is still showing the total for the most recent date in the column. not the grand total of the sum from most recent date for each store.

[Phil_Seamark]

Oh ok, hmmm,  which store is wrong?  I wondered if the dates in your example data might have been a bit different to what you were explaining.

[erhodes]

However, the table from Phil appears to have worked in my test, I'm going to try and add all the totals I need to the and see if it works. Thanks again.

[Sean]

@erhodes   Okay I've updatedmy MEASURES like this...

Most Recent Total Score 2 =
CALCULATE (
    SUM ( 'Table'[Score] ),
    LASTDATE ( 'Table'[Date] ), VALUES ( 'Table'[Store] ), ALL ( 'Table' )
)

# Of Names 2 =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Name] ),
    LASTDATE ( 'Table'[Date] ), VALUES ( 'Table'[Store] ), ALL ( 'Table' )
)

Hope this helps!