Post reply

Can I SUM the 'LastPeriod' query results?

  • April 8, 2013 at 7:57 pm

    #291927

    Hi,

    I want to create 'Rolling' Month on Month, Quarter on Quarter queries with Revenue data.

    So, for example if I want to create a Quarterly Reveneue variance (which I would calculate as the Last months Revenue minus the First Months Revenue & divide that revenue amount in to the Last months Revenue amount)

    My Quarterly query therefore needs to include the 'End Month' let's say June 2012 & contain the 'Billed_Amount' field to find the Revenue for a given month.

    I thought I'd use the 'Lastperiod' function to collect 3 months back from June 2012 & SUM those amounts to use in a variance calculation with the next quarter & so on.

    Here's my MDX query that I'm checking in SSMS to ensure it's correct before I add it to the Calculations in BIDS;

    SELECT LastPeriods(3,[MTH_QTR_HLF_YR].[Month].&[201206]) ON 0

    FROM [RM RADAR ACCT CUBE]

    This gives me the months I need & counts the records, OK so far...

    Then I add the 'Total_Billed' Measure to get the Revenue figures;

    SELECT LastPeriods(3,[MTH_QTR_HLF_YR].[Month].&[201206].[Measures].[Total Billed]) ON 0

    FROM [RM RADAR ACCT CUBE]

    I don't get anu results for this query, not sure why & I want to SUM these 3 'Total_Billed' amounts once this query works.

    So, I'm thinking I could then use this to SUM the 'Total_Billed';

    SELECT SUM(LastPeriods(3,[MTH_QTR_HLF_YR].[Month].&[201206].[Measures].[Total Billed])) ON 0

    FROM [RM RADAR ACCT CUBE]

    Can anyone suggest how I best use 'Lastperiod' OR should I use another Function altogether??

    Thanks

  • April 8, 2013 at 10:27 pm

    #1604656

    Duplicate post. Direct replies here: http://www.sqlservercentral.com/Forums/Topic1440114-391-1.aspx

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply