Can't calculate columns and measure from two tables... going crazy...

[bardbq]

Hi,

First post here... I've been struggling two days on this so far and I'm about to go crazy...

Here is my problem:

I use two tables:

'tabItem' (all existing items)

'tabSales Invoice Item' (all invoice lines)

I want to have a column that displays the projected remaining quantities of each items in 60 days (it takes about 60 days to get our order after we place it) based on the quantity of items sold in the selected period. I got everything working except that last column...

First, I find the total quantity sold for each item (measure):

total_qty_sold = SUM('tabSales Invoice Item'[qty])

Then, I find the number of days between the first date and last date at which each item was sold within the selected date range (measure):

days_sold = CALCULATE(DATEDIFF([min_date];[max_date];DAY))

min_date (measure): min_date = CALCULATE(MIN('tabSales Invoice Item'[creation]))

max_date (measure): max_date = CALCULATE(MAX('tabSales Invoice Item'[creation]))

After that, I calculate the average quantity sold in 60 days (measure):

sold_per_60days = CALCULATE(60 * DIVIDE([total_qty_sold];[days_sold]))

Lastly, in the 'tabSales Invoice Item' table, I've got a column [item_code] for which I created a "many to one (*:1)" relation to 'tabItem'[name]. I needed this relation because in the 'tabItem' table, I have a column [total_in_stock] that tells me the current remaining quantity for each item.

I created a new calculated column in 'tabSales Invoice Item' that get the remaining quantity from the other table:

total_avail_stock_qty = RELATED(tabItem[total_in_stock])

Now that I have all that, all I should have to do would be this:

proj_qty_60days = 'tabSales Invoice Item'[total_avail_stock_qty] - 'tabSales Invoice Item'[sold_per_60days]

And this should give me the current quantity available minus the quantity I should sell in the next 60 days... But it doesn't work... Even though [sold_per_60days] return the correct value in the visualisation table, when I use it in my last calculation, it returns no value at all and the result of the calculation is the same as ('total_avail_stock_qty' - 0).

This is getting me crazy because ALL THE INFO IS RIGHT THERE, right in front of my eyes in the visualisation table... All I need to to make that easy substraction between the 'total_avail_stock_qty' table and the 'sold_per_60days' column... It's all there... 

PLEASE HELP ME!

[bardbq]

up

[Gajo]

I have exactly the same problem, but haven't gotten any further with it...

[bardbq]

I'll keep working on this today, will let you know if I find a solution... please do the same!

Thanks!

G.

[Gajo]

I've been banging my head into this wall for at least a week now.... Don't think I'll solve it today 😞

[bardbq]

Pretty sure the issue is because the slicer can't get a single value from the related table. Seems like I'm getting an array of values (wich are all the same) and so the slicer can't choose which one to use and it returns a 'null' value. Might be a problem with my table relation...

[bardbq · Accepted solution]

Problem solved!

I was using a calculated column to get my current available stock quantity. I created a measure "total_stock_available" that gets the MAX value of all the values in my calculated column:

total_stock_available = MAX('tabSales Invoice Item'[total_avail_stock_qty])

I can now use that measure to make my calculation!