Sum Sales between Dates

[#1]

Hi All,

I'm really struggling to sum the sales between two dates and was wondering if anyone could help and explain what I'm doing wrong.

So, I've got 5 fields, Customer, Start Date, End Date, CalendarMonthAndYear, Sales

*Start Date/EndDates will vary for each customer*

When adding the below calculated dimension, the result is as expected - Yes and No where it should be...

=if([Audit Sales End] > CalendarMonthAndYear and CalendarMonthAndYear >= [Audit Sales Start],'Yes','No')

I replicated this as an expression and changed the 'yes' to sum(sales) & removed 'no', but it's not providing the result I'm after.

I've since tried to adjust this as it appears that if isn't correct, and used something like this instead but it's still not correct

Sum(({<$CalendarMonthAndYear ={">=$([Start Date])<=$([End Date])"}>}, Sales)

Any help would be much appreciated, and it would be good to understand why what I have tried isn't working...

Thanks

Ben

[vishsaggi]

What is your CalendarMonthAndYear field format i mean what format is this field in it should match with your Start date and End date fields?