1 of 1 people found this helpful
you were right there just need to add some calculations
the formulas are
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.
(also adding Jim Van Sistine)
Thank you so much!
I am now trying to calculate the change from prior month-end and change from prior year-end, but my attempt did not work.
I tried creating the following calculating fields and adding them as additional columns:
(Inventory on Max Date) - (Inventory on max date zz second most recent) = Month Change
(Inventory on Max Date) - (Inventory on max date zz prior) = YTD change
Is this not possible or is there a solution?
I have attached the workbook you used in which I attempted to add delta columns.
Since you haven't specified an ELSE value in your initial calcs (totally fine), you'll need to put some null handling in your delta ones.
ZN([inventory on max date])-ZN([inventory on max date zz second most recetn])
That forces a null result to be 0 and then it should calculate correctly.
Alternative would be to put ELSE 0 into your inventory calcs.
IF [Date]=[zzz Prior Year End Actuals] then [Inventory] ELSE 0 end
Either of these should work for what you want to do.
That worked perfectly.
One last question:
Is there a way to stack the Delta from Prior Year-End label and value on top of the Delta from Prior Month-End label and value (like below) instead of label beside the value?
Attached the file as well!
Delta from Prior Year-End 900 Delta from Prior Month-End -300
This worked perfectly!