Future startups

[cajste]

Hello,

I have a data modell containing among other things sales date and start date and I need to calculate the number of future startups at any givet date that were sold on or before that date. I would like to show it as a pivot table with year and month as rows and the logic of the measure is that for each row calculate all future startups, i.e. has a startdate that is greater than the maximum date in the row context, and at the same time were sold before or on the maximum date of the row context. How do I set that measure up?

Example data below and also an example of the desired output.

Any help appreciated!
Caj

Example data

[TABLE="width: 184"]
<tbody>[TR]
[TD="align: right"]Salesdate[/TD]
[TD="align: right"]Startdate[/TD]
[/TR]
[TR]
[TD="align: right"]2017-01-27[/TD]
[TD="align: right"]2017-02-10[/TD]
[/TR]
[TR]
[TD="align: right"]2017-02-02[/TD]
[TD="align: right"]2017-02-16[/TD]
[/TR]
[TR]
[TD="align: right"]2017-02-14[/TD]
[TD="align: right"]2017-02-28[/TD]
[/TR]
[TR]
[TD="align: right"]2017-01-24[/TD]
[TD="align: right"]2017-03-01[/TD]
[/TR]
[TR]
[TD="align: right"]2017-01-20[/TD]
[TD="align: right"]2017-03-20[/TD]
[/TR]
[TR]
[TD="align: right"]2017-03-27[/TD]
[TD="align: right"]2017-06-01[/TD]
[/TR]
[TR]
[TD="align: right"]2017-01-19[/TD]
[TD="align: right"]2017-10-01[/TD]
[/TR]
[TR]
[TD="align: right"]2017-03-14[/TD]
[TD="align: right"]2017-10-01[/TD]
[/TR]
</tbody>[/TABLE]

Desired output

[TABLE="width: 184"]
<tbody>[TR]
[TD]Month[/TD]
[TD]Future startups[/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] 1[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD] 2[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD] 3[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD] 4[/TD]
[TD="align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]

 

[CalcSux78]

If you had a helper column in column C where C2 formula=Month(A2)
And copy that data to the end of your dataset, you should be able to select the entire range and insert pivot table.

When Setting up, put "Salesdate" in the report filter, the new "Salemonth" in the row labels, and "Startedate" in sum values boxes.