Dividing by the number of months in period

[bigck2]

Hello,

I've been working on a PowerPivot model and one of my measures 'Rent PSF', takes Rental Revenue divided by the Occupancy %, and then divides by square footage.

This works fine as long as I'm working with single months as a time period. However, if I shape the pivot table to display for a quarter or year time frame, my 'Rent PSF' measure no longer works properly. To work properly for a quarterly basis I have to divide the Rent PSF by 3. To work properly for an annual basis I have to divide the Rent PSF by 12.

My solution has been to build separate measures 'Rent PSF - monthly', 'Rent PSF - quarterly', 'Rent PSF - semi annual', and 'Rent PSF - annual'. This kind of works for me, but ultimately this is not that great when I want to build a PivotTable /Report with a combination of months and quarters or months and years.

Is there some way I can correct the 'Rent PSF' measure so it divides by the number of months being evaluated?

Thanks,

Chris

 

[Matt Allington]

How about this. Create a new measure that counts how many months are in the current filter context, then divide the current formula by this new measure. If the number of months is 1, then it will give you the same answer as you get now. If there are more than one month in play, it will cater for them.

 

[bigck2]

Thanks Matt!

I was able to complete this using a measure that calculates the number of months in the period. Here is the DAX formula I used:

[Count of Months] =COUNTROWS( VALUES( DimCalendar[Month] ) )

My only precaution is how this might work partially through the period. For example, if I'm trying to calculate Rent PSF on a quarterly basis for Q3 when I only have actuals for July and August. I think dividing by this [Count of Months] would be dividing by 3 because there are 3 months in the quarter, when I should be dividing by 2. Anyways, I'll just have to test and see if this is an issue or not.

Thanks again for your help!

Cheers,

Chris

 

[Matt Allington]

Yes, that is the way. Regarding the 2/3 month thing, it depends on your calendar table and also how you are using filter context to filter the data. Eg if you have a full year 2015 in your calendar table and have the full year in the filter context in the pivot, then yes it will be a problem. But both of these can be solved. Here is a post I wrote about how to build a calendar in Power Query that automatically ends at the current point in time (solving the calendar over run issue). Create a Custom Calendar in Power Query - PowerPivotPro PowerPivotPro