Don't sum an Inventory value

[Anonymous]

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.

[Mikelytics]

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.

[Ashish_Mathur]

Hi,

Share the download link of your PBI file and show the expected result very clearly.

[Anonymous]

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.

https://github.com/Adityadambal/PowerBI/blob/c11e19e34563f036bef3399bdf0e915eb5b3a125/Inventory%20Pl...

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

Hi @Ashish_Mathur , 

Thanks for the solution. Just one modification needed.

My 'Plan Inv' shows inventory of December 2022, while my 'Actual Inv' shows inventory of the current month when no date filter is selected.

Is there a way 'Plan Inv' can show data of the month of 'Actual Inv' when no filter is selected?

[Ashish_Mathur]

You are welcome.  Why not maintain the discipline of always select a year or year and month?

[Anonymous]

Yes, since date is floating value (i.e., Every Month new data), I would like to have one new month data appearing on the table.

[Ashish_Mathur]

Your reply does not address my previous question.  Just avoid the complication by ensuring that you always select a Year or a Year and month.

[Anonymous]

@Ashish_Mathur , Thanks for the response. 

I have a scenario:

I will select November 2022 and save the file, and publish it in Power BI service. Next month, the filter will still be showing November 2022, rather than December 2022.  It can become more tricky as January comes around where a previous year with 2 months earlier (November) data will be showing. 

So, I was wondering if 'Plan Inv' could show same timeline as the 'Actual Inv' (which always shows current month, thanks to your .pbix file solution)?

This would alleviate my above issue.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[HoangHugo]

Hi

try Function TOTALMTD( ), or CALCULATE(your measure, DATESMTD) to calculate Inventory of latest month, in filter context.