Forum Discussion
Running SUM using different tables
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, 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
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!
