locked
Current Month subtracted by the previous month for 12 months RRS feed

  • Question

  • I am building a statement of cash flows. Part of this requires subtracting results for the current month by the final results of the previous month. So it would look like this:

          Jan Feb  Mar
    account  Janₓ- Decₓ Febₓ - Janₓ Marₓ - Febₓ


    Right now I am doing that by having a calculation for each month. this is starting to become cumbersome for excel to process every time I make an update, so I think I need to find a way to use one calculated field with a month field added as columns, but I am unsure how to write this. 

    Any idea how to accomplish this? 

    Monday, April 18, 2016 11:29 PM

Answers

  • Make sure you have a date table in your model and use time intelligence functions.

    If you have a calculation [Results], you can calculate the results for the previous month with

    ResultsPrevMth:=CALCULATE([Results],PREVIOUSMONTH(Date[Date]))

    and obviously

    FinalResult:=[Results]-[ResultsPrevMth]

    For output, you would have a column [Month] in your date table and use this in a pivot table or chart.

    • Proposed as answer by Charlie Liao Tuesday, April 19, 2016 9:00 AM
    • Marked as answer by Charlie Liao Wednesday, May 4, 2016 2:06 PM
    Tuesday, April 19, 2016 6:49 AM
    Answerer

All replies