Running SUM using different tables

[PaulusD]

Hi all,

As a relatively new BI user, i've been able to find numerous solutions already using this forum, so thanks for your continuous contributions :).

New i'm running into a knowledge issue on my behalf, hoping someone has the magic tip ;-).

What i'm trying to accomplish:

Prediction of stock level next month.

What data I have

Stock level table (monthly dump)

Sales table

purchase orders table

turnover table (one for planned turnover, one for billed turnover)

work in progress table

Idea is to have:

Sum of stock level for in current month

+

sum of work in progress in current month

+

sum of purchase orders expected in current month

-

sum of planned sales in current month

-

sum of billed sales in current month

All tables are linked via general date table (marked as a date table). so far this work perfectly when laying out all the values next to each other.

Unfortunately I cant share the data as it's confidential information.

Thanks in advance for your help!

[v-lili6-msft · Accepted solution]

hi, @PaulusD 

For your data model, if these five tables are all linked with date table,

If so just drag a year month column from date table and then create a measure 

measure= Sum of stock level +sum of work in progress +sum of purchase orders expected - sum of planned sales 

- sum of billed sales

For conditional ( in current month),  you could add a calculate column to judge if this month is  in current month.

then use if as a slicer.

If not your case, please use virtual data to create a simple pbix file and share it with expected output for us.

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,

Lin

[PaulusD]

Hi @v-lili6-msft , thanks for your reply.

I did't realize it would be that easy!

I indeed added a calculate to test if the measue is in the current month and that seems to work fine.

Now for the magic;

What would really make this great is predicting stock levels for the coming +-6 months (per month)

Do i need to create new measure for each time frame? (so for month +1, month +2, month +3 etc.)

I would use the predicted stock for month +1, add:

work in progress for month +1

Purchase orders for month +1

and subtract

sales month +1

and that should predict the stock levels for month +2.

So would I need to repeat this say 5/6times? or could i manage this dynamically by saying show the above for the coming 6 months?

Thanks again 🙂

[v-lili6-msft]

hi, @PaulusD 

You do not need to repeat this say 5/6times, just keep the date table has the full date.

For example:

Yearmonth       Measure

2019-01           100

2019-02           150                Assume that this is the current month

2019-03           200

2019-04           150

2019-05            120

2019-06           100

2019-07            90

2019-08            80

For the red part, As long as the date table has the full date, it will be calculated dynamically.

Best Regards,

Lin

[PaulusD]

Hello Lin,

I ve tried it as you explained it but unfortunately I can't get it to work.

What does work is elaborating on your first reply and creating multiple measure with dynamic date references (so month +0, month +1 month +1 etc).

Bit of a work around but it gets the job done.

Thanks!