Forum Discussion
Helper INeed help with Dax - calculate (pull) a value based on max date time aggregate to certain fields
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 |
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)
3 Replies
- Greg_Deckler
Community Champion
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=125990248778&search-result-uid=985814
- amitchandak
Super User
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)
dhirendra59Thank you so much amitchandak
