none
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

Answers

  • 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

    "All2Sober" wrote in message news:0203b5d3-5aad-4a24-9967-99a48d50504e@communitybridge.codeplex.com...

    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

    "All2Sober" wrote in message news:0203b5d3-5aad-4a24-9967-99a48d50504e@communitybridge.codeplex.com...

    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