How always get latest value for particular Id ?

[cyberirbis]

Hi all,

I need to create measure to calculate cumulative sum of quantity field which always shows value for the end of selected period.

Source data is like this:

OpportunityId	DateKey	Quantity
1	20191101	2
1	20191215	-1
1	20200129	0
1	20200130	0
1	20200131	1
2	20200101	0
2	20200102	0
2	20200103	1

There are quantity values by date, but some date might be missing.

Output of my report should be like this(instead of month could be year, quarter,  or any other related non-date dimension - in this case it's should be total for whole table):

Month	Quantity
Nov 2019	2
Dec 2019	1
Jan 2020	3

To do this I have column which calculates running total for each opportunity:
TotalQuantity = CALCULATE(
SUM('Opportunity Daily Snapshot'[Quantity]),
ALL('Opportunity Daily Snapshot'),
'Opportunity Daily Snapshot'[DateKey]<=EARLIER('Opportunity Daily Snapshot'[DateKey]),
'Opportunity Daily Snapshot'[OpportunityId]=EARLIER('Opportunity Daily Snapshot'[OpportunityId])
)

Result:

OpportunityId	DateKey	Quantity	TotalQuantity
1	20191101	2	2
1	20191215	-1	1
1	20200129	0	1
1	20200130	0	1
1	20200131	1	2
2	20200101	0	0
2	20200102	0	0
2	20200103	1	1

Now I need some measure/column to calculate sum(TotalQuntity)  by Month, using last  TotalQuantity value in each month.
I tried to do this in measure:
Test:=
CALCULATE (
SUM (TotalQuantity ),
FILTER ( ALL ( 'Opportunity Daily Snapshot'), 'Opportunity Daily Snapshot'[DateKey] = MAX ('Opportunity Daily Snapshot'[DateKey]) ),
VALUES ('Opportunity Daily Snapshot'[OpportunityId])
)
But problem, is that some opportunities don't have any values on the last day of month, which is returned by MAX ('Opportunity Daily Snapshot'[DateKey]). 

Is there any way to filter by max(Datekey) for particular OpportunityId in the selected period(Month, Quarter etc)?
Or is there any other way to get only latest row for each OpportunityId in current selection?

[Greg_Deckler]

@cyberirbis - Did you get a resolution? If so, please mark as answer, otherwise, @ me.

[cyberirbis]

@Greg_Deckler 

Hi, I've tried this, but it doesn't resolve my particular issue.

Finally I fixed it by changing source table. I added records on each day for each OpportunityId. It return correct values, but in some cases query fails fith out of memory error

[amitchandak]

@cyberirbis , try like

sumx(summarize(table,table[OpportunityId],"_1" ,lastnonblankvalue(table[DateKey],sum(Table[Quantity]))),[_1])

[Greg_Deckler]

@cyberirbis - This is basically Lookup Min/Max if I understand correctly.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434