# Monthly Subscription Revenue Totals Using Only Start and End Dates

### Question

• Hare is a question that appears simple in concept but for which I can not figure out how to implement. I want to create a report in Power Pivot which shows total revenue by each customer (rows) by months in the year (columns). The trick is that I only have the start and end date of each customer's subscription and the monthly revenue to be recognized, rather than a data point for each month.

I can do this with a hacked together combo of if/then-vlookup,-sumif formulas that basically goes month by month and asks whether the start date is before and the end date i after THAT particular month, if this is true it then plugs the revenue amount into that month. This works, but is very ugly. I woudl lilke to do it within the PowerPivot framework.

Can this be done? Can anyone help?

Wednesday, August 04, 2010 3:46 PM

• This can be done in DAX (nearly everything can be done with it:) )

Check out this post http://www.powerpivotblog.nl/use-slicer-values-in-a-calculation-with-powerpivot-dax it covers a similar problem. The problem with these solutions is that Sumx and Filter are very CPU expensive.

Tuesday, August 10, 2010 2:04 PM
• Hi

You need to have the data for the individual months for this to work with a Pivot Table.

Creating the report without a PT isn't difficult.
In row 1
Name    Start    End    Subscription    31/01/2010    28/02/2010 etc
The dates in column E onward, are end of months dates. The cells cam be formatted Custom>"mmm-yy" if required

The in cell E2 enter
=IF(SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDIRECT(\$B2&":"&\$C2)),
ROW(INDIRECT(DATE(YEAR(E\$1),MONTH(E\$1),0)+1&":"&E\$1)),0))),\$D2,0)
Copy across for as many columns as required and down for as many rows.

Basically, if any day of the period from start to end falls within a given month, then the monthly subscription is allocated to that month.

If you wanted to allocate the subscription according to how many days of the subscription fell within that month, then use
=SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDIRECT(\$B2&":"&\$C2)),
ROW(INDIRECT(DATE(YEAR(E\$1),MONTH(E\$1),0)+1&":"&E\$1)),0)))*\$D2*12/365

Regards
Roger Govier

Hare is a question that appears simple in concept but for which I can not figure out how to implement. I want to create a report in Power Pivot which shows total revenue by each customer (rows) by months in the year (columns). The trick is that I only have the start and end date of each customer's subscription and the monthly revenue to be recognized, rather than a data point for each month.

I can do this with a hacked together combo of if/then-vlookup,-sumif formulas that basically goes month by month and asks whether the start date is before and the end date i after THAT particular month, if this is true it then plugs the revenue amount into that month. This works, but is very ugly. I woudl lilke to do it within the PowerPivot framework.

Can this be done? Can anyone help?

________ Information from ESET Smart Security, version of virus signature database 5346 (20100806) ________

The message was checked by ESET Smart Security.

http://www.eset.com

________ Information from ESET Smart Security, version of virus signature database 5346 (20100806) ________

The message was checked by ESET Smart Security.

http://www.eset.com

Roger Govier Microsoft Excel MVP
Saturday, August 28, 2010 9:40 AM

### All replies

• This can be done in DAX (nearly everything can be done with it:) )

Check out this post http://www.powerpivotblog.nl/use-slicer-values-in-a-calculation-with-powerpivot-dax it covers a similar problem. The problem with these solutions is that Sumx and Filter are very CPU expensive.

Tuesday, August 10, 2010 2:04 PM
• Hi

You need to have the data for the individual months for this to work with a Pivot Table.

Creating the report without a PT isn't difficult.
In row 1
Name    Start    End    Subscription    31/01/2010    28/02/2010 etc
The dates in column E onward, are end of months dates. The cells cam be formatted Custom>"mmm-yy" if required

The in cell E2 enter
=IF(SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDIRECT(\$B2&":"&\$C2)),
ROW(INDIRECT(DATE(YEAR(E\$1),MONTH(E\$1),0)+1&":"&E\$1)),0))),\$D2,0)
Copy across for as many columns as required and down for as many rows.

Basically, if any day of the period from start to end falls within a given month, then the monthly subscription is allocated to that month.

If you wanted to allocate the subscription according to how many days of the subscription fell within that month, then use
=SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDIRECT(\$B2&":"&\$C2)),
ROW(INDIRECT(DATE(YEAR(E\$1),MONTH(E\$1),0)+1&":"&E\$1)),0)))*\$D2*12/365

Regards
Roger Govier

Hare is a question that appears simple in concept but for which I can not figure out how to implement. I want to create a report in Power Pivot which shows total revenue by each customer (rows) by months in the year (columns). The trick is that I only have the start and end date of each customer's subscription and the monthly revenue to be recognized, rather than a data point for each month.

I can do this with a hacked together combo of if/then-vlookup,-sumif formulas that basically goes month by month and asks whether the start date is before and the end date i after THAT particular month, if this is true it then plugs the revenue amount into that month. This works, but is very ugly. I woudl lilke to do it within the PowerPivot framework.

Can this be done? Can anyone help?

________ Information from ESET Smart Security, version of virus signature database 5346 (20100806) ________

The message was checked by ESET Smart Security.

http://www.eset.com

________ Information from ESET Smart Security, version of virus signature database 5346 (20100806) ________

The message was checked by ESET Smart Security.

http://www.eset.com

Roger Govier Microsoft Excel MVP
Saturday, August 28, 2010 9:40 AM