4 Replies Latest reply: Jan 23, 2013 3:12 PM by Andrew Landry RSS

    12 Month Lookback

      Hi all!


           I'm working on a Financial Dashboard for our company that will be providing Profit and Loss statements verses printing out a huge stack of paper P&L statements.  One of the requests was to have a 12 month lookback with an P&L statement for example:



      P& L Heading
      Jan 2013December 2012November 2012
      LINE HAUL REVENUE$1234.00$1222.00$1423.00
      ACCESSORIAL REVENUE$300.00$200.00$600.00
      TOTAL REVENUE$1534.00$1422.00$2023


      I already have a tab that shows the P&L Headings with the current month but I'm having trouble with the 12 month lookback.


      So my dimension is P&L Heading and my first expression is this (which works):

      Sum({<PL_LineType={'e'},MTHSTART={'=$(vMaxMonth)'}>} [Amount])+Sum({<PL_LineType={'i'},MTHSTART={'=$(vMaxMonth)'}>} [Amount]*-1)

      Where vMaxMonth is =max(MTHSTART). (PL_Linetype is P&L Headings that match 'e' are expenses and 'i' are income lines.)


      So how would I go programming the second expression with 1 month less than the selected one?


      Thanks in advance!