The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Dear friends,
I have data that need to summarize, but at one of its column, I needed to show the last date (maximum date) on the related key row.
The data is something like this :
and the expection is to summarize this ItemId, Quantity, CostAmount, which is the 2 last column is easy (just use SUM), but the TransDate I want to retrieve only the last date when quantity is positive, like this :
I tried like this ->
Invetory = ADDCOLUMNS(
SUMMARIZECOLUMNS('inventory data'[ItemId], "Qty", SUM('inventory data'[Quantity]), "Amount", SUM('inventory data'[Cost amount]) ),
"InflowDate", MAX('inventory data'[TramsDate])
)
But the date is not the date of related ItemId, but the Max date overall, not to mentioned I don't know where to put the condition of the Quantity should be > 0.
Thanks
Solved! Go to Solution.
@admin_xlsior , Try like
Invetory = ADDCOLUMNS(
SUMMARIZECOLUMNS('inventory data'[ItemId], "Qty", SUM('inventory data'[Quantity]), "Amount", SUM('inventory data'[Cost amount]) ),
"InflowDate", MAXX(filter('inventory data', [ItemId] = earlier([ItemId])),[TramsDate])
)
@admin_xlsior , Try like
Invetory = ADDCOLUMNS(
SUMMARIZECOLUMNS('inventory data'[ItemId], "Qty", SUM('inventory data'[Quantity]), "Amount", SUM('inventory data'[Cost amount]) ),
"InflowDate", MAXX(filter('inventory data', [ItemId] = earlier([ItemId])),[TramsDate])
)
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
83 | |
73 | |
51 | |
42 |
User | Count |
---|---|
140 | |
112 | |
72 | |
64 | |
63 |