Forum Discussion
Resolver IIIFind Last Value by MAX date and SUM
I have an inventory audit table. I created a measure to find the qty of the last record date for each warehouse and bin, however the grand total is showing 0 but I want it to SUM that measure. For the example below, I want the grand total to show 37.
Qty Last Value = CALCULATE(SELECTEDVALUE('Inventory Audit By Date'[Qty On Hand]), 'Inventory Audit By Date'[Date] = MAX('Inventory Audit By Date'[Date]))
*Date Max is also a measure
Date MAX = MAX('Inventory Audit By Date'[Date])
Thanks for the reply from tamerj1 and Greg_Deckler.
Hi rachaelwalker ,
Based on your description, I created a simple example data and rewrote your measure to achieve the desired result.
Here are my steps:
1.Create a simple data:
2.Create a measure, use SUMX to calculate total.
Qty Last Value = SUMX( SUMMARIZE( 'Table', 'Table'[Warehouse], 'Table'[WarehouseBin], "Lastvalue", CALCULATE(SELECTEDVALUE('Table'[QtyOnHand]), 'Table'[Date]= MAX('Table'[Date])) ), [Lastvalue] )3.The final result is as follows:
Best Regards,
Zhu
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
3 Replies
- Greg_Deckler
Community Champion
rachaelwalker First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8 - tamerj1
Hi rachaelwalker
Please vote for Greg_Deckler's idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742eYou may try
Qty Last Value = SUMX ( SUMMARIZE ( 'Inventory Audit By Date', 'Inventory Audit By Date'[Product ID], 'Inventory Audit By Date'[Warehouse], "@Qty", SUMX ( TOPN ( 1, 'Inventory Audit By Date', 'Inventory Audit By Date'[Date] ), 'Inventory Audit By Date'[Qty On Hand] ) ), [@Qty] ) Thanks for the reply from tamerj1 and Greg_Deckler.
Hi rachaelwalker ,
Based on your description, I created a simple example data and rewrote your measure to achieve the desired result.
Here are my steps:
1.Create a simple data:
2.Create a measure, use SUMX to calculate total.
Qty Last Value = SUMX( SUMMARIZE( 'Table', 'Table'[Warehouse], 'Table'[WarehouseBin], "Lastvalue", CALCULATE(SELECTEDVALUE('Table'[QtyOnHand]), 'Table'[Date]= MAX('Table'[Date])) ), [Lastvalue] )3.The final result is as follows:
Best Regards,
Zhu
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
