Forum Discussion
Don't sum an Inventory value
Hi,
I have a situation with a matrix, where I am comparing plan vs Actual Inventory by month for 4 categaries.
But when I select a whole year, the inventory values of all months get summed up for Plan and Actual column. This would be wrong for checking Inventory, as it indicates stock at a point of time (i.e, the values should not sum up when I choose a year, but show the latest month's figures). Is there a way to show this with a "Year, Month" filter on?? PFA the screenshot.
11 Replies
Hi
try Function TOTALMTD( ), or CALCULATE(your measure, DATESMTD) to calculate Inventory of latest month, in filter context.
Hi,
Share the download link of your PBI file and show the expected result very clearly.
Hi Ashish_Mathur , Thanks for replying. I have attached link for my github which has the .pbix file.
My preferred outcome is:
1. When the Matrix appears, I want it to show latest month's inventory. And I should be able to select a specific month from the filter to see that month's inventory.
Hi Anonymous m
Did you try writing a measure like this?
Last Date Value = CALCUALTE( SUM(Table[Value Column]), LASTDATE(table[Date Column] )The date column needs to have a date format. The measure checks the dates in scope (based on your filter) and selects the last avaiable. Then it takes only the sum for the values for this last date. I use it in nearly every scenario where I work with balance values. There might be some changes required based on your requirements and data model but in general that should to it when I udnerstand your request correctly.
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
