locked
Measure for previous months of a selection RRS feed

  • Question

  • Hi,

    in my workbook I've imported an order fact table and a time dimension, this containing a date (pk), year, month and day. For simplicity, the order fact table has the order number, the order date and the amount. I've linked the order date and the date of the time dimension. I've created two slicers, one for the year of the time dimension and one for the month of the time dimension.

    I need to calculate the amount of the previous month respect to year and month selected, the two month preceeding respect to year and month selected, ..., so I've tried this formula but unsuccessfully and with an error:

    Calculate(sum(Order[Amount]); DATESBETWEEN(Order[OrderDate];EDATE(STARTOFMONTH(VALUES(OrderDates[Date])); -1); EDATE(ENDOFMONTH(VALUES(OrderDates[Date]));-1) ) )

    I've tried to change this formula in other manners obtaining the total amount.

    Any suggests to me, please?

    Thanks

    Monday, November 19, 2012 12:26 PM

Answers

  • Ok, different approach for your dynamic requirement.

    1. Create a "MonthsPrior" table containing a row for each number of months you'd need to calculate.  In my model, I added a linked table with 1,2,3,4,5,6,7,8,9,10,11 and 12.  Don't have to create any realtionships with this table. 

    2. Define the following measure on your Orders table:

    =IF(HASONEVALUE(MonthsPrior[MonthsPrior]) ,CALCULATE([SumAmount]
    ,PARALLELPERIOD(DimDate[Date], (-1 * VALUES(MonthsPrior[MonthsPrior])), MONTH)) ,BLANK() )

    For single month periods like you want, ParallelPeriod is way simpler than the manual date calcs. And notice that the second parameter for ParallelPeriod is decrementing by the number that's chosen in the MonthsPrior table.  To accomplish this, the MonthsPrior table must be filtered to a single value.  So you're best off putting MonthsPrior in a slicer or in the filter on the pivot.

    Final product would look like the image below and would allow you to have interactive control of the lookback in months, instead of having several static measures defined for 3 months back, 6 months back, 9 months, etc. Only catch is that if you want to see several copies of that measure, you can't reuse the single dynamic measure in a single pivot. If you need to see 3,6 and 9 next to each other, better off creating those flavors of the measure with the hard-coded number of months to roll back.

    Let me know if that works for you.


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


    Thursday, November 22, 2012 6:04 AM
    Answerer

All replies

  • For your formula above, you'll always want to use your date table instead of your Order table as the first param to DATESBETWEEN (or any other time intelligence function). 

    Also, here's a different approach to the previous month calculated measure:

    =CALCULATE([SumAmount],PREVIOUSMONTH(DimDate[Date]))

    And the previous 2 months calculated measure:

    =CALCULATE([SumAmount]
               ,DATESBETWEEN(DimDate[Date]
                             ,DATEADD(FIRSTDATE(DimDate[Date]),-2,MONTH)
                             ,FIRSTDATE(DimDate[Date]) - 1
                            )
              )

    Let me know if that's what you were looking for.


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


    • Edited by Brent GreenwoodEditor Monday, November 19, 2012 8:58 PM
    • Proposed as answer by Elvis Long Wednesday, November 21, 2012 7:09 AM
    • Unproposed as answer by pscorca Wednesday, November 21, 2012 12:24 PM
    Monday, November 19, 2012 8:58 PM
    Answerer
  • Ok, but with a date as 15/11/2012 I need to calculate the amount related to september month.

    This measure functions:

    =CALCULATE([SumAmount]
               ,DATESBETWEEN(DimDate[Date]
                             ,DATEADD(FIRSTDATE(DimDate[Date]),-2,MONTH)
                             ,DATEADD(LASTDATE(DimDate[Date]),-2,MONTH)
                            )
              )

    But also I need to calculate 3 months back and so on. I'd like to have a dynamic decrement for the DATEADD function.

    Thanks



    • Edited by pscorca Wednesday, November 21, 2012 1:22 PM
    Wednesday, November 21, 2012 12:32 PM
  • Ok, different approach for your dynamic requirement.

    1. Create a "MonthsPrior" table containing a row for each number of months you'd need to calculate.  In my model, I added a linked table with 1,2,3,4,5,6,7,8,9,10,11 and 12.  Don't have to create any realtionships with this table. 

    2. Define the following measure on your Orders table:

    =IF(HASONEVALUE(MonthsPrior[MonthsPrior]) ,CALCULATE([SumAmount]
    ,PARALLELPERIOD(DimDate[Date], (-1 * VALUES(MonthsPrior[MonthsPrior])), MONTH)) ,BLANK() )

    For single month periods like you want, ParallelPeriod is way simpler than the manual date calcs. And notice that the second parameter for ParallelPeriod is decrementing by the number that's chosen in the MonthsPrior table.  To accomplish this, the MonthsPrior table must be filtered to a single value.  So you're best off putting MonthsPrior in a slicer or in the filter on the pivot.

    Final product would look like the image below and would allow you to have interactive control of the lookback in months, instead of having several static measures defined for 3 months back, 6 months back, 9 months, etc. Only catch is that if you want to see several copies of that measure, you can't reuse the single dynamic measure in a single pivot. If you need to see 3,6 and 9 next to each other, better off creating those flavors of the measure with the hard-coded number of months to roll back.

    Let me know if that works for you.


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


    Thursday, November 22, 2012 6:04 AM
    Answerer
  • Hi Brent, thanks for your reply.

    I think that your measure is named ParamPriorMonthSales, isn't it?

    Moreover, in my Order table I've values from january to june 2011 but when I drop in row labels year and month from time dimension I can see only june.

    However, when I decide the number of previous month (3, 6, 9, ...) I want to parameterize the related measures without writing the decrement.

    Fe I think to have 1PriorMonthOrders, 2PriorMonthOrders, 3PriorMonthOrders with the related formula parameterized.

    Thanks

    Thursday, November 22, 2012 5:30 PM
  • Pscorca -

    I don't follow how you're only seeing June on your rows axis.  Can you post a screenshot of your model and of the resulting pivot you are seeing?  Or if possible, can you just share the workbook with sample data on your Skydrive?  That would make it easier to get to a solution.

    Also, not sure I follow exactly what you're after.  Perhaps you could mock it up in Excel by just typing in what you'd like to see and sharing a screenshot.

    Thanks.


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


    Thursday, November 22, 2012 7:27 PM
    Answerer