cumulative stock with dax

[Anonymous]

HI,

How i can have cumulative planned stock calculated with dax on weekly level.

so below initial stock would be planned stock based on initial stock+planned production - total demand plan

br. J

[v-juanli-msft]

Hi

From your matrix, I guess your dataset is like this, right?

If it is in this case, first create a calculated column in original table to get the planned stock value

planned stock =
CALCULATE (
    MAX ( [value] ),
    FILTER (
        ALLEXCEPT ( Table1, Table1[weekly level] ),
        [category] = "Initial stock"
    )
)
    + CALCULATE (
        MAX ( [value] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[weekly level] ),
            [category] = "Planned production"
        )
    )
    - CALCULATE (
        MAX ( [value] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[weekly level] ),
            [category] = "Total demand plan"
        )
    )

Second

Create a new table

Table 2 =
UNION (
    SUMMARIZE ( Table1, Table1[weekly level], Table1[category], Table1[value] ),
    SUMMARIZE (
        Table1,
        Table1[weekly level],
        "category", "Planned stock",
        "value", SELECTEDVALUE ( Table1[planned stock] )
    )
)

Finally the matrix shows the “Planned stock” on the row and its value.

In addition, it is noted that when we use calculated column in matrix to show value of 'Planned stock', its value can't change by any slicer or filter.

Best Regards

Maggie

Best Regards

Maggie

[Anonymous]

Thanks Maggie!

Issue is that I have 3 dataset's which are gathered to this matrix. Would that calculation be working still?

And what happens to planned stock when initial stock is still "0" in future? Cause it should always use current week initial for planned stock

BR. J