Running Total based on 2 Measures and 1 Calculated Column

[joshua1990]

Hi all!

I have 2 simple measures:

• Stock Level 
• Planned Orders

The first one shows me the Stock Level as of today. It is based on a inventory table that is linked to a calendar table with date = today.

Planned Orders is a measure that shows me for specific dates in the future the expected orders.

Then I have a attribute table that contains all articles. In the same table we have the forecast as a calculated column. It shows the expected forecast per week.

Now I would like to create a matrix that shows me the following information:

Year-Week	Current Stock Level	Forecast	Orders	Expected Stock Level
2022-44	7500	500	1500	8500
2022-45		500	0	8000
2022-46		500	0	7500
2022-47		500	150	8500

Now, how can I calculated the last column?

It is based on Stock + Planned Orders - Forecast.

Can we use here the new DAX function OFFSET?

Thanks

[v-yueyunzh-msft]

Hi , @joshua1990 

According to your needs, you first need to determine whether only the first row of your [Current Stock Level] has data, or do you need to judge according to the [Year-Week] field?

Second, if you want to implement mutual calculation of measures, you can use the SUMMARIZE()+ALLSELECTED() function to create a virtual table of the measures, and then calculate them in the virtual table.

For the accumulation calculation, you also need to have a column that can distinguish the corresponding time-size relationship.

For the accumulation calculation of the measure needs to be calculated in combination with the corresponding model, can you share your .pbix file (remove sensitive data) to us, so that we can better help you~

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly