Need help with Dax - calculate (pull) a value based on max date time aggregate to certain fields

[dhirendra59]

Hello All, I need help to create a measure to pull total on hand balance value agregated by warehouse and item number based on max date-time for that particular day with filter transaction type 10. 

It should give value 1547 for 7/27, 1576 for 7/28 and 1631 for 7/29 and so on. 

Please let me know if you need more information. See attached data

warehouse	item_number	stock_transaction_type	transaction_identity	total_on_hand_balance	transaction_datetime
700	20115	10	WOP	1547	7/27/2020 7:37
700	20115	10	WOP	1438	7/27/2020 7:38
700	20115	10	WOP	1653	7/27/2020 7:38
700	20115	10	WOP	1576	7/28/2020 8:28
700	20115	31	OID	1635	7/28/2020 15:06
700	20115	31	OID	1617	7/28/2020 15:12
700	20115	10	WOP	1640	7/29/2020 7:19
700	20115	11	WOM	1631	7/29/2020 8:47
700	20115	31	OID	1558	7/29/2020 10:34
700	20115	31	OID	1540	7/29/2020 10:36
700	20115	31	OID	1522	7/29/2020 15:23

[amitchandak · Accepted solution]

@dhirendra59 , Have date column and try

date = [transaction_datetime].date

new measure = calculate(lastnonblankvalue(table[transaction_datetime],min(table[total_on_hand_balance])),allexcept(Table,Table[warehouse],Table[item_number], Table[transaction_identity], Table[Date]), Table[stock_transaction_type]=10)

[dhirendra59]

Thank you so much @amitchandak 

[Greg_Deckler]

@dhirendra59 - Seems like either a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

Or perhaps Lookup Min/Max: https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814?search-action-id=1...