topic Re: How always get latest value for particular Id ? in DAX Commands and Tips

How always get latest value for particular Id ?

cyberirbis — Tue, 01 Sep 2020 11:59:01 GMT

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?

Re: How always get latest value for particular Id ?

Greg_Deckler — Tue, 01 Sep 2020 12:26:55 GMT

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

Re: How always get latest value for particular Id ?

amitchandak — Tue, 01 Sep 2020 13:00:12 GMT

cyberirbis , try like

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

Re: How always get latest value for particular Id ?

Greg_Deckler — Mon, 07 Sep 2020 05:51:07 GMT

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

Re: How always get latest value for particular Id ?

cyberirbis — Wed, 07 Oct 2020 12:55:28 GMT

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