Set Analysis - Sales Order Quantity Prior to Date Selections

[#1]

Hi all,

In a Pivot Table I wish to provide:

- Sales Order Quantity PRIOR to the Current Date Selections to provide an Opening Balance. The Date Selections are list boxes Month and Year.

Sales Order Quantity within the Current Date Selections is easy of course, but i'm not sure how to sum the Sales Order Quantity for all Dates (Years and Months) Prior to the current selection.

Any ideas?

Thanks!

[#2]

Pseudocode:

Sum of SalesOrderLineOrderedQuantity where the Year is less than the Currently selected year

Formula(incorrect)

sum({$<Year= {<$(Year)}>}SalesOrderLineOrderedQuantity)

Pseudocode

Sum of SalesOrderLineOrderedQuantity where Month is less than the Currently selected Month.

I want to combine these two into the one formula.

[#3 · Accepted solution]

This was the solution:

Sum({$<Year=, Month=>} If(Date < vMaxDate,SalesOrderLineOrderedQuantity))

it's getting hard to get a response on here with the number of posts created increasingly dramatically!