Hi
I have created a database of unshipped orders at each weekday. I.e all the unshipped orders on the 01st June, all the unshipped orders at 02nd June etc, in the data, i have added a report date field that shows what day the report relates to.
In this data, the orders have a specific status, entered, booked etc... what i want to see is that for each order - what was the last date that the order was in "entered" status before it was changed.
I know that you would use Calculate(Max-Report Date) - but i am unsure how to structure the rest of the formula.
In the attached example, i would want to return report date 23/01/2019 as that was the last date that it was in "Entered" status.
Help Appreciated
Hi Liam,
You're on the right track, you can use this formula:
=CALCULATE( MAX([Report Date]), Table1[Line Status]="Entered" )
Mynda
Hi Mynda
Thank you for replying so promptly.
However, when using the above formula, it only returns a result when the current status is "entered".
In reality i want to see it the other way round:
if the status is "entered", i can see how many days since the order was created by deducting order creation data from report date - no issues.
However, when the status is <> "entered", i want to be able to see the last day it was in "entered" status before it changed.
In the example, rows 18 and 19 would have a date of 23/01/2019 as that was the last day it was in "entered" status.
Hi Liam,
It's not clear if you want to see this data in a PivotTable or in a column in the source data table. I thought you'd want it in a PivotTable initially, but now you mention rows 18 and 19 I'm thinking you want to see this date on those rows???
If the latter, you'd be best to use Power Query to extract the last entered dates into another table, you can then use RELATED function to bring it into the source data table, or you can use a PivotTable.
All examples in file attached.
Mynda
