locked
Revenue Schedules - Creating rows by number of months RRS feed

  • Question

  • Hi, if anyone can help me figure this out, I would be very happy!!

    I am trying to calculate how much revenue we may get, based on potential new business opportunities.

    The core fields we have are

    • Total Contract Value ($ or £)
    • Duration of contract (months)
    • Revenue start date
    • Various information about the new business - ID, Title, Customer etc

    We can easily calculate the revenue per month with "Total Contract value divide by duration".

    However what I would really like to do is be able to know how much revenue we will be getting each month.

    To do this I was thinking we should probably create a new row for each month entry, with the mm-yyyy being the only difference for each row. But I have no idea how to do this, and to create the appropriate months and the correct amount of rows.

    Can anyone help please? I'm happy to use Power query or power pivot!

    Tuesday, May 26, 2015 4:30 PM

Answers

All replies

  • Classic example of a tally table or calendar table.  You actually answered your own question (give yourself points!). I don't think you'd need Power Pivot, straight T-Sql works.

    Create a Months table and populate with a loop or some clever T-Sql.

    CREATE TABLE Months (MonthDate Date)

    DECLARE @MonthInt Int
    DECLARE @YearInt Int
    DECLARE @Loop INT = 1

    SET @MonthInt = Month(GetDate())
    SET @YearInt = Year(GetDate())

    WHILE @YearInt < 2020
    BEGIN
        INSERT INTO Months
        SELECT CAST(CAST(@YearInt AS Char(4))+ Replicate('0',2-LEN(@MonthInt)) + CAST(@MonthInt as varChar(2)) + '01' AS Date)
        
        IF @MonthInt % 12 = 0
        BEGIN
            SET @YearInt += 1
            SET @MonthInt = 1
        END
        SET @MonthInt +=1
    END

    Then you can cross join or inner join where Months.MonthDate BETWEEN StartDate and DateAdd(mm, StartDate, DurationofContract).


    PaulBarbin

    Wednesday, May 27, 2015 4:17 AM
  • Hi Dominic,

    According to your description, you need to add a new row for each month entry, right?

    In a PowerPivot data model, we can create calculated fields and calculated measures to data model, however we cannot add a row to Pivot table directly. So in your scenario, you can add a calculated field to get the month and year, and then count the rows for each month.
    =MONTH([Date])&"-"&YEAR([Date])
    =CALCULATE(COUNT(case05272[Amount]),ALLEXCEPT(case05272,case05272[MonthYear]))

    Reference
    http://www.ashishmathur.com/perform-different-calculations-in-the-subtotalgrand-total-column-of-a-pivot-table/

    If this is not what you want, please provide us some sample data, so that we can make further analysis.

    Regards,


    Charlie Liao
    TechNet Community Support


    Wednesday, May 27, 2015 7:25 AM
  • Hi Dominic,

    my understanding of your request is that you want to allocate the potential revenue for different opportunities into the months of the year(s).

    If that's the case, you should calculate an additional column: RevenueEndDate (RevStartDate+Duration)

    Then for every month you need to check if the month is in the valid timespan.

    In Power Query you could generate lists with dates on every row of you opportunity table or merge with a table that contains the months you're interested in the cartesian way (every line with every line - I'm using calculated binder-columns with the value "1" in each table)

    In Power Pivot this can be achieved by using a disconnected Date-table, see some examples here:

    https://social.technet.microsoft.com/Forums/en-US/3c0ed4e6-bb54-4efb-9fd3-8b950dfccea0/create-a-step-chart-using-powerpivot?forum=sqlkjpowerpivotforexcel

    https://social.technet.microsoft.com/Forums/en-US/dc245558-a16e-43ae-9745-172a405ab787/inventory-turnover-calculation-on-movements-basis?forum=sqlkjpowerpivotforexcel

    However, to apply this to your example you need to find a way to filter on your 2 different date-columns in one line.

    Or you start in PQ by adding one new row per opportunity with the RevEnd-Date in the Date-Column to your table and apply the PP examples as they are.


    Imke


    Wednesday, May 27, 2015 8:48 AM
    Answerer