cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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

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?

1 ACCEPTED SOLUTION
Employee

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')
)```

Proud to be a Datanaut!

6 REPLIES 6
Employee

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')
)```

Proud to be a Datanaut!

Community Champion

@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!

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.

Employee

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.

Proud to be a Datanaut!

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.

Community Champion

@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!

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors