Sum all earliest values by field

[sdb_utd]

Hi,

I have an invoice table which contains a company name, a sale vaue and a date. There's also a filter column which users will toggle on the report.

I'm trying to get a few figures:

1. Difference between earliest and latest price per for each name. I have this as a measure called "Difference"

2. The overall percent change from first to last for all names. This should be -12.46%, and should be just sum of the Difference measure, but sum will not accept a measure. Any ideas?

Raw data example

Name	Value	Filter	Date
United	250	y	01/01/2020
MPC	90	y	01/01/2020
SDB	150	y	01/01/2020
FPL	70	n	01/01/2020
CHEM	80	y	01/01/2020
United	50	y	02/01/2020
United	80	y	03/01/2020
MPC	1000	y	02/01/2020
MPC	100	y	03/01/2020
MPC	45	n	04/01/2020
SDB	100	y	02/01/2020
SDB	200	y	03/01/2020
FPL	100	y	02/01/2020
FPL	65	y	03/01/2020
CHEM	500	y	02/01/2020
CHEM	85	y	03/01/2020
United	225	y	04/03/2020

Report example

Name	FirstValue	LastValue	Total Name values	Total all values	Percent total spend	Difference	%GT Difference
FPL	70	65	235	3190	7.37	-7.14%	-3.23%
SDB	150	200	450	3190	14.11	33.33%	15.05%
United	250	225	605	3190	18.97	-10.00%	-4.52%
CHEM	80	85	665	3190	20.85	6.25%	2.82%
MPC	90	45	1235	3190	38.71	-50.00%	-22.58%

[v-shex-msft]

Hi @sdb_utd,

Can you please provide your measure formula for test? If will help to test and modify your formula on sample data.

How to Get Your Question Answered Quickly  

In addition, you can also take a look at the following blog about measure total issue:

Measure Totals, The Final Word  

Regards,

Xiaoxin Sheng

[sdb_utd]

https://www.dropbox.com/s/ddvxc6y9psou0du/sample%20data.pbix?dl=0

I think I have this working now. My previous measure for summing the earliest and latest values was just summing the values at the earliest or latest date in the table, ie. summing all values for 01/01/2020 or all values for 04/03/2020, rather than the latest value for a name.

Sum of Total  at Latest Date = 
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        'Table',
        'Table'[Name]  = ( 'Table'[Name] )
        &&
        'Table'[Date]  = CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table','Table'[Name]))
           )
)

[amitchandak]

@sdb_utd ,

I did not get your last column. Rest of the cal is in file.https://www.dropbox.com/s/xc9lp45fx76j1do/FirstLastvalue.pbix?dl=0

I have used firstnonblankvalue and lastnonblankvalue, those are part of lastest release

Appreciate your Kudos.