Sum Sales Between Two Dates

[joshdellapietro]

Hi,

I am currently using the following formula to calculate total gross sales between the effective date and 6 months prior:

Sum({<[Invoice Date]={">=$(=MonthStart(Addmonths(Min([Effective Date]),-6)))<=$(=MonthEnd(Addmonths(Min([Effective Date]),-1)))"}>}[Gross Sales])

This formula is working,  however, in my pivot table, I am using dimensions that have different effective dates and the formula seems to be locking into one date for all data and not reacting based on the dimension's effectiev date.

Please help!

[anthonyj]

Hi @joshdellapietro ,

Some things I'd try to debug your calculation would be to duplicate your pivot table and add your date parameters in as measures and see what they are returning.

The Monthstart() function has it's own date shifting parameter so you can take out the addmonths().

Add as measures

date(MonthStart(Min([Effect Date]), -6))

Regards

Anthony

and 

date(MonthStart(Min([Effect Date]), -1))

and see if there are different dates appearing in the rows of the pivot table.