Skip to Content
Sep 21, 2020 at 05:54 AM

how to calculate a monthly stock balance

201 Views Last edit Jun 21, 2021 at 12:36 PM 3 rev

Here's my issue - how to plan my monthly stock without using a data action (I want to use a calculated measure but I'm not sure how to make it).
I have a monthly planning table, with the following measures:

  • Actual stock (only in current month)
  • Sales monthly plan
  • Production monthly plan

The next months should look like this

  • Stock plan = Previous month's stock (minus) Previous month's sales (plus) Previous month's production
  • Sales monthly plan
  • Production monthly plan

I calculated the Stock-plan above, as a data action with Foreach.
However my client is not happy with the solution as it requires to run the data-action hundred of times when planning production. He expect to see the Stock immediately adjusted when typing numbers in production.

Therefore I would have done a formula in the account dimension or a calculated measure, but I'm not sure how to make it when it's time dependent (taking calculation from previous month) and also a calculation that begins after the actual data I have. (current month)

I hope that it's clear

Many Thanks for your help