Overall Business Problem - graph value of unused inventory that is sitting on shelves
I have a Cal Column, "Running Total Highest Primkey Calc Column" that takes the value of the running total (column not shown) with the highest prikey for the month.
I need a measure that dynamically "carrying forward" this value for a particular Product/Material, even if there are no more rows in the dataset for that particular Product/Material.
The logic I believe I want is, for each Material:
- If next period does not have any records for that Material, carry forward previous period's ending total
-Else If the next period does have records for that Material, then take the value from the next period with the highest primary key for that Material
In my attached screenshot, I'm showing the current measure that does the "carry forward" bit, but I don't want it to sum, rather I need it to follow the logic above.
In attached screenshot, Oct in 2022 should be sum of Product A's value [11,502.54] + Product B's value from September [1080.27] = 12,582.81. This value should be carried forward "forever" since there are no more records for either Material.
I hope this made sense - I feel like the solution is out there and I have been looking at this for too long!
TIA
I have a Cal Column, "Running Total Highest Primkey Calc Column" that takes the value of the running total (column not shown) with the highest prikey for the month.
I need a measure that dynamically "carrying forward" this value for a particular Product/Material, even if there are no more rows in the dataset for that particular Product/Material.
The logic I believe I want is, for each Material:
- If next period does not have any records for that Material, carry forward previous period's ending total
-Else If the next period does have records for that Material, then take the value from the next period with the highest primary key for that Material
In my attached screenshot, I'm showing the current measure that does the "carry forward" bit, but I don't want it to sum, rather I need it to follow the logic above.
In attached screenshot, Oct in 2022 should be sum of Product A's value [11,502.54] + Product B's value from September [1080.27] = 12,582.81. This value should be carried forward "forever" since there are no more records for either Material.
I hope this made sense - I feel like the solution is out there and I have been looking at this for too long!
TIA
