Forum Discussion

dhirendra59's avatar
dhirendra59
Icon for Helper I rankHelper I
6 years ago
Solved

Need 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 t...
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    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)

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

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