How to join a value from another table in a calculated column (alternativ a measure)

[gehe_bbe]

Hello to All,

am struggling with getting a value from another table (InitialStock) in den the table (Sales). I need a custom column or a suitable Measure. I have two main tables, Sales and InitialStock which are connected bei Model over the third table Model. The InitialStock has no date, but a marker vor ActualPeriod (true/false) to distinguish the ActualPeriods InitialStock and the PrePeriods Values.
After getting this InitialStock I want do create a table which ist summarized bei MonthID and Model with the values SalesQty and InitialStock - but the InitialStock should not be summed... I hope it's understandable what I need.

This is the data model an relations

I hope anyone could helb me soon, I appreciate your help, thanks a lot.

Steve

[v-junyant-msft · Accepted solution]

Hi @gehe_bbe ,

May I ask if your requirement is to add this column to the table Sales?

If yes, then you can use Power Query for that:

And the final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-junyant-msft · Accepted solution]

Hi @gehe_bbe ,

May I ask if your requirement is to add this column to the table Sales?

If yes, then you can use Power Query for that:

And the final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gehe_bbe · Accepted solution]

Hi 

thanks for this approach, at the moment am not sure if it's good to join the tables, but yes, this is a possible way. Afer researching on internet I found out that it could be done in this way:

InitialStock = CALCULATE(SUM(InitialStock[StockQty]),FILTER(ALL(InitialStock),InitialStock[Branch]=EARLIER(Sales[Branch]) && InitialStock[Model] = EARLIER(Sales[Model]) && InitialStock[ActualPeriod] = EARLIER(Sales[ActualPeriod])))

I tried it out in the file you send me and it works fine 😊 The name of thie ERALIER function is a little bit confusing but it gets the value of the row in the Sales table which can be uses for the filter.

I don't know how to attach the modified file...

Regards Steve

[lbendlin]

I have two main tables, Sales and InitialStock which are connected bei Model over the third table Model.

consider appending these tables (or their quantities) instead.  If you put the initial stock as a positive number and the sales quantity as a negative number then you can easily calculate the remaining stock at any point in time.

[gehe_bbe]

Thanks, but I have other stock movements like purchase, rearrangements, inventory correctures etc.