Expression to show stock levels on a date

[mathew_newns]

HI,

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.

any support would be greatfully received.

thanks

[Wlad_Masi]

Have you tried with Count() instead of Sum()?
Did you get the same wrong numbers?

[mathew_newns]

Hi,

thanks for the suggestion. Count() returns a very similar number to sum(), they are both about 50-70% of the value i'd expect.