Total on measure not correct - I think its a sumX issue just not sure where I'm going wrong...

[Alexbienvenue]

Hello all and thank you for any advice/help you can give. After spending countless time on this forum and youtube without any luck I'm hoping someone might be able to lend a hand and pass some advice to what im doing wrong. Heres the first easy part: the problem: 

Table on the bottom left clearly isnt summing those totals the way i'd like them. The measure for how its getting those is in the measures bar.

This is where I am so far:

Not sure if thats the right direction? I feel like ive tried a lot of different ways but im unsure of how i should finish the summarize expression and then finish the SumX expression. All i want to do is recreate the table on the left exactly but have it sum the correct total. 

Thanks to any and all help!!

[Whitewater100 · Accepted solution]

Hi:

Sorry if I'm way off but can you try

Bottle Inventory = SUMX('Bottle Inventory', 'Bottle Inventory'[amount])

I do see you have a variable about the ID. how does that factor in? Also can you add them all together or do you have to just add the amount of inventory on the latest date for each ID?

Then it might be:

  CALCULATE(SUM('Bottle Inventory'[amount]), 

  LASTDATE(Dates[Date]))    *assuming you have Date Table named Dates and the date field is named Dates..

I hope this helps..

[Alexbienvenue]

This worked! I went back thorugh my data and noticed that the two SKUs that were not showing up becuase they were not in the same month as the rest of the SKUs. I forgot to update them to the most current month.

Just for anyone reading this just relaize that all values will display will with LASTDATE but if you want them to sum on the table using this they do have to be in the same time, in this care for me it was months.   

[Alexbienvenue]

@Whitewater100 And yes - I am just trying to get the most recent amount of inventory from each SKU and am not summing up all months of a SKU together.

I am also trying to avoid using any kind of date slicer where I have to manually hit the date to display that inventory. I just want the inventory from the last date to appear and then be summed.

Thanks for the help so far though!

[Whitewater100]

Hi:

I beleive this will work very well. Especially if you can use the date table. 

Total Inventory(not really using except in measure) = SUM('Bottle Inventory'[amount])

Ending Inv =

CALCULATE(

[Total Inventory],

LASTNONBLANK(

PARALLELPERIOD('Date Table'[Date],

0,MONTH),

[Total Inventory])

)

I will attach an example file with two ways to handle this issue.

https://drive.google.com/file/d/1azlIq03AEgCDWD5l5beaNDY_esAFhnRB/view?usp=sharing

[Alexbienvenue]

@Whitewater100 Well that got me much closer but there is still one weird thing going on...

So I copied the tables side by side and used your measure:

CALCULATE(SUM('Bottle Inventory'[Amount]),LASTDATE('Bottle Inventory'[Binv_Date]))

Only difference is I used the dates in the inventory table instead of a generic date table

However for some reason two of the SKUs that are in that category are not showing? the actual total should be 737 but the final two SKUs in the right side table arent registering. Any idea why that would be happening? Doing exactly what I want except for those two not adding to the total. I rechecked my data in access and they are labled correctly for that category and are appearing on the visual filters pane. 

[Whitewater100]

Hi:

You can try this version but using your date table should help. (You can mark as Date Table). These time intel functions count on the contiguous date table.

Product Inventory = SUM(('Bottle Inventory'[amount])

End Inv = CALCULATE({Product Inventory]),

  LASTDATE(Dates[Date],
[Product Inventory]))

or

End of Month On Hand Inv =

CALCULATE(
                SUM('Bottle Inventory'[Amount]), 
LASTNONBLANK(
       Dates[Date], 
CALCULATE(COUNTROWS('Bottle Inventory')

Starting from bottom of month..Counts rows and  if no value it goes up to the last date with inventory

If you have multiple stores with different End Inv dates there are other ways to do this.