I have a report with multiple Items numbers and dates for when it was sold. I am trying to create a calculation that displays the number of days since the last time that item was sold.
I have tried messing around with the previous date and days between but there are times I get a previous date for a different item number.
A is what I am producing. B is what I am looking for.
within the data shown in the table,
A seems to be the correct solution, why are you looking for B ?
I would like it to display the previous date (if available) by Item.
Currently, it shows the previous date in the block.
did you add the dimension „item“ in the syntax of the previous-function ?
I have tried “=Previous([Date] ForEach([Item]))” but get the same results.
I think about the previous-function-syntax not the calculation-context.
try with „reset_dims“
Sorry, what do you mean by that?
You should use In instead of ForEach
Either that or, if acceptable, simply change your block to sort by item first.
“=Previous([Date] In([Item]))” brings back alot of #multivalue.
Looking at the table on the right, do you simply need to check if the previous item matches the current row item and if it does, do a DaysBetween calculation? Something like:
=If(Previous([item]) = [item]; DaysBetween(Previous([Date];[Date]);-1)
Then manage the -1 with conditional formatting to blank them out.
