topic Re: Sum Sales Between Two Dates in App Development

Sum Sales Between Two Dates

joshdellapietro — Sat, 12 Mar 2022 16:13:15 GMT

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!

Re: Sum Sales Between Two Dates

anthonyj — Mon, 14 Mar 2022 22:37:18 GMT

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.