Answered by:
Measure for previous months of a selection
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 hardcoded 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 Edited by Brent GreenwoodEditor Thursday, November 22, 2012 6:04 AM
 Proposed as answer by Ed Price  MSFTMicrosoft employee Sunday, October 27, 2013 7:24 AM
 Marked as answer by Ed Price  MSFTMicrosoft employee Monday, November 25, 2013 7:15 AM
Thursday, November 22, 2012 6:04 AMAnswerer
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 PMAnswerer 
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 hardcoded 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 Edited by Brent GreenwoodEditor Thursday, November 22, 2012 6:04 AM
 Proposed as answer by Ed Price  MSFTMicrosoft employee Sunday, October 27, 2013 7:24 AM
 Marked as answer by Ed Price  MSFTMicrosoft employee Monday, November 25, 2013 7:15 AM
Thursday, November 22, 2012 6:04 AMAnswerer 
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 Edited by Brent GreenwoodEditor Thursday, November 22, 2012 7:27 PM
 Proposed as answer by Ed Price  MSFTMicrosoft employee Monday, November 25, 2013 7:15 AM
Thursday, November 22, 2012 7:27 PMAnswerer