I have warehouse data for all pallets received and dispatched over time. this includes the received date, dispatched date and pallet id amongst other fields. i am trying to look at the pallet stock holding on a particular day/week/month in the past in a table or graph and then be able to filter it by customer and product. there is a data bridge in place that has a canonical date loaded and linked through the data tables if that helps.
the following expression returns a number but does not match the longhand exel figures i have
sum(If (CanonicalDate>=RecDate and CanonicalDate<=[Last Used Date],1,0))
I've been through many of the posts on similar subjects but can't get to a solution that works.