How to find difference between first date value Vs the last date value for each of the item?

[AnshulGupta]

I have business forecast every month appended as one table; Below is one extremely simplified example:

Date	SalesPerson	Item	Value
5/1/2020	ABC	MM	100
8/1/2020	ABC	MM	30
8/1/2020	XYZ	NN	50
9/5/2020	ABC	QQ	60
9/5/2020	XYZ	NN	70

What I would like to do is to find the delta forecast for different fields whether here SalesPerson Or Item, between First and the Last Forecast. So, as an example, for SalesPerson it would look like:

SalesPerson	First Day Forecast	Last Day Forecast	Difference
ABC	100	60	-40
XYZ	0	70	70
TOTAL	100	130	30

Similar may go for Item or it could be combination of SalesPerson & Item as well. Of course, actual table has tons of other fields and any combination may be needed in the resulting table to see what/who is gaining or is in loss, between first and last date.

I see lot of nice solutions but either they mostly seem to work on total (not individual row item) OR I get values on first available date (so, I don't see 0 for XYZ on First day forecast as in above table) or I get the total of all dates; none of these would work for me though.

Your expert help is deeply appreciated.

[amitchandak]

@AnshulGupta , Try like

First Day Forecast = if(min(Table[Date]) =max(Table[Date]),0 , firstnonblankvalue(Table[Date],max(Table[Value])))
Last Day Forecast= lastnonblankvalue(Table[Date],max(Table[Value])))
diff = [Last Day Forecast] -[First Day Forecast]

or with row context

First Day Forecast = sumx(Values(Table[SalesPerson]), if(min(Table[Date]) =max(Table[Date]),0 , firstnonblankvalue(Table[Date],max(Table[Value]))))
Last Day Forecast = sumx(Values(Table[SalesPerson]), lastnonblankvalue(Table[Date],max(Table[Value]))))
diff = sumx(Values(Table[SalesPerson]),[Last Day Forecast] -[First Day Forecast])

[AnshulGupta]

Sorry, doesn't work. Same issue as reported to Greg
- XYZ on First Day should come 0 but it picks the first available value the next day as 50. I already raised concern on that in original query (see expected table in original query)

I need a result table which looks like in my query

[amitchandak · Accepted solution]

@AnshulGupta , please find the attached file an after signature

check for First First and Last Last measures in table

[AnshulGupta]

Works Great. Thank you so much!!!

[Greg_Deckler]

@AnshulGupta I did this in three measures:

First Day = 
    VAR __SalesPerson = MAX('Table (19)'[SalesPerson])
    VAR __Min = MIN('Table (19)'[Date])
RETURN
    SUMX(FILTER(ALL('Table (19)'),[SalesPerson]=__SalesPerson && [Date]=__Min),[Value])



Last Day = 
    VAR __SalesPerson = MAX('Table (19)'[SalesPerson])
    VAR __Max = MAX('Table (19)'[Date])
RETURN
    SUMX(FILTER(ALL('Table (19)'),[SalesPerson]=__SalesPerson && [Date]=__Max),[Value])



Forecast Difference = [Last Day] - [First Day]

PBIX is attached after sig. Table (19), Page 19. Note, if you are doing this to prove that all sales people lie, that's already a given. 😛

[AnshulGupta]

Thank you so much. 

Unfortunately, I see 3 trouble here

1) XYZ on First Day should come 0 but it picks the first available value the next day as 50. I already raised concern on that in original query (see expected table in original query)

2) Total is not right either on First Day (comes blank) or on last day (comes max) (see expected table in original query)

3) This is only for SalesPerson. I would at least in this simplified table need for item as well. But in actual table I have many different fields. It would be quite cumbersome looking to have some 20 items define into variables and then to use them in Return if condition