Calculating Inventory from 2 transaction tables at different level of aggregation

[Anonymous]

[EDIT] : Added Data Sample : Data Sample 

Hi All,

This is my first post so please be nice 😉

I am trying to build a report to see the evolution of inventory over time in Qty and Value. I have the history of transactions since the beginning of the operations which reside in 2 different tables (receiving and Selling), my simplified data model look like below :

I have created the following measures :

_COGS=sum('Table Selling'[Cost]))

_Receiving_Value = sum('Table Receiving'[Cost])

_Qty_Sold = sum('Table Selling'[Qty])

_Qty_Received = sum('Table Receiving'[Qty])

_Inventory_Mvt_Qty =[_Qty_Received]-[_Qty Sold]

_Inventory_Mvt_Value = [_Receiving_Value]-[_COGS]

_SOH_Calculated = if(firstnonblank('Table Calendar'[Date],1)<Today(),calculate([_Inventory_Mvt_Qty], filter(all('Table Calendar'),'Table Calendar'[Date]<=Max('Table Calendar'[Date]))))

_Inv_Value_Calculated = if(firstnonblank('Table Calendar'[Date],1)<TODAY(),CALCULATE([_Inventory_Mvt_Value],FILTER(all('Table Calendar'),'Table Calendar'[Date]<=max('Table Calendar'[Date]))))

When I look at aggregate number, everything seems to works very well, but when I start to look by location (I have 4 different locations where we held inventory) number get completely weird and the value by location does not work, see below an example of the output for one location on december 31st (sorry I have to anonymise the data): 

The location Code come form the Table Location, Items come from the Table Item and Date from Table Calendar. The last Column in the table shows what should be the correct Value.

What I don't understand is :

• where does the empty lines come from
• why I have items in the LOC1 that should not be there.

I am sure I am missing something obvious, and kind of think it is link with some relationships, but cannot get my head around after hours looking into it hence why I am reaching out.

Worth noting that I am using Direct Query, and I cannot change the data via Power Query not having Calculated column/Table.

Thanks for any help

Chris

[Anonymous]

Hi,

Anyone has some suggestion please pretty please

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

I checked your data and found that your formula is fine, but column Table Receiving[Item Number] is not fully included in Table Grouping[Item Number], which results in row with empty item in the final result.

Take the example of inventory value, on the left side are the data from the Table Receiving and Table Selling tables respectively, the third table contains Table Location[Location Code], Table Grouping[Item Number] and [_Inventory_Mvt_Value].

As you can see, there are no items Item 1386, Item 1387 and Item 29 in Table Location, so in the third table there are empty item row and the value is 10910.83 + 9746.43 + 4138.55 = 24795.81.

In the Table Receiving, the value of Item5 is 22231.61 and in the Table Selling the value of Item5 is 0, so the result of the calculation is 22231.61 - 0 = 22231.61.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi,

I knew it was a simple reason, completely missed that, I check the original data and found out that there was a filter in the SQL query, now filter is remove everything works like a charm

Thanks