Forum Discussion
Running SUM using different tables
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!
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
4 Replies
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
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 :)
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
