How to show the Opening Stock for a week as the Closing Stock for the previous week?

k_r — Tue, 03 Dec 2024 11:29:43 GMT

Hi there,

I currently have this table that I've created using Original Opening Stock and Closing Stock measures.

Besides Week Index 0, going forward from Week Index 1, I would like to have the Opening Stock for a week as the Closing Stock for the previous week. So for example, Week Index 2's Original Opening Stock should be equal to Week Index 1's Closing Stock.

Is there a way to acheive this without circular dependency errors?

The current DAX formula I have for the two measures are as follows:

Original Opening Stock:
CALCULATE(
SUM(Stock_CLEAN[Unrestricted PAC]),
Stock_CLEAN[Material] = SELECTEDVALUE(Stock_CLEAN[Material]),
Stock_CLEAN[Plnt] IN {"1100", "1500"}
)

Closing Stock:
[Opening Stock] - [Total Demand] + [Total Supply]

Re: How to show the Opening Stock for a week as the Closing Stock for the previous week?

bhanu_gautam — Tue, 03 Dec 2024 11:57:05 GMT

k_r , Try using

Opening Stock =
VAR CurrentWeek = SELECTEDVALUE(Stock_CLEAN[Week Index])
VAR PreviousWeek = CurrentWeek - 1
VAR PreviousWeekClosingStock =
CALCULATE(
[Closing Stock],
Stock_CLEAN[Week Index] = PreviousWeek
)
RETURN
IF(
CurrentWeek = 0,
[Original Opening Stock],
PreviousWeekClosingStock
)

topic Re: How to show the Opening Stock for a week as the Closing Stock for the previous week? in DAX Commands and Tips

How to show the Opening Stock for a week as the Closing Stock for the previous week?

Re: How to show the Opening Stock for a week as the Closing Stock for the previous week?