Forum Discussion

PaulusD's avatar
PaulusD
Icon for Resolver II rankResolver II
7 years ago
Solved

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...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    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

     

     

v-lili6-msft's avatar
v-lili6-msft
Icon for Community Support rankCommunity Support
7 years ago

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's avatar
PaulusD
Icon for Resolver II rankResolver II
7 years ago

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 :)