Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |