Calculating Market Statistics - Difference from Time Periods

Hi Anonymous,

You can take a look at below measures formula :

>>How to calculate difference from previous periods (quarters, months, etc.); this would be for supply and absorption

Diff of Previous Quarter(RBA) =
var currClassTier= LASTNONBLANK(OfficeStats[ClassTier],[ClassTier])
var currSubmarket=LASTNONBLANK(OfficeStats[Submarket],[Submarket])
var currDate=MAX([Quarter])
var currQuarter=ROUNDUP(MONTH(currDate)/3, 0)
var temp=FILTER(ALL(OfficeStats),[ClassTier]=currClassTier&&[Submarket]=currSubmarket)
return
SUMX(FILTER(temp,ROUNDUP(MONTH([Quarter])/3,0)=currQuarter&&YEAR([Quarter])=YEAR(currDate)),[RBA])-
if(currQuarter>1,
SUMX(FILTER(temp,YEAR([Quarter])=YEAR(currDate)&&ROUNDUP(MONTH([Quarter])/3,0)=currQuarter-1),[RBA]),
SUMX(FILTER(temp,YEAR([Quarter])=YEAR(currDate)-1&&ROUNDUP(MONTH([Quarter])/3,0)=4),[RBA]))

Diff of Previous Quarter(Vacant SF) =
var currClassTier= LASTNONBLANK(OfficeStats[ClassTier],[ClassTier])
var currSubmarket=LASTNONBLANK(OfficeStats[Submarket],[Submarket])
var currDate=MAX([Quarter])
var currQuarter=ROUNDUP(MONTH(currDate)/3, 0)
var temp=FILTER(ALL(OfficeStats),[ClassTier]=currClassTier&&[Submarket]=currSubmarket)
return
SUMX(FILTER(temp,ROUNDUP(MONTH([Quarter])/3,0)=currQuarter&&YEAR([Quarter])=YEAR(currDate)),[Vacant SF])-
if(currQuarter>1,
SUMX(FILTER(temp,YEAR([Quarter])=YEAR(currDate)&&ROUNDUP(MONTH([Quarter])/3,0)=currQuarter-1),[Vacant SF]),
SUMX(FILTER(temp,YEAR([Quarter])=YEAR(currDate)-1&&ROUNDUP(MONTH([Quarter])/3,0)=4),[Vacant SF]))

>>I also need to be able to track year over year changes, or absorption to date if possible

Diff of Previous Year(RBA) =
var currClassTier= LASTNONBLANK(OfficeStats[ClassTier],[ClassTier])
var currSubmarket=LASTNONBLANK(OfficeStats[Submarket],[Submarket])
var currDate=MAX([Quarter])
var temp=FILTER(ALL(OfficeStats),[ClassTier]=currClassTier&&[Submarket]=currSubmarket)
return
SUMX(FILTER(temp,YEAR([Quarter])=YEAR(currDate)),[RBA])-SUMX(FILTER(temp,YEAR([Quarter])=YEAR(currDate)-1),[RBA])

Diff of Previous Year(Vacant SF) =
var currClassTier= LASTNONBLANK(OfficeStats[ClassTier],[ClassTier])
var currSubmarket=LASTNONBLANK(OfficeStats[Submarket],[Submarket])
var currDate=MAX([Quarter])
var temp=FILTER(ALL(OfficeStats),[ClassTier]=currClassTier&&[Submarket]=currSubmarket)
return
SUMX(FILTER(temp,YEAR([Quarter])=YEAR(currDate)),[Vacant SF])-SUMX(FILTER(temp,YEAR([Quarter])=YEAR(currDate)-1),[Vacant SF])

>>For each quarter, and under each submarket, I need to be able to add up Class A Tier, Class A Tier 2, and Class B (grouped together - AKA "Investment Grade Inventory").

GT of Quarter(RBA) =
var currSubmarket=LASTNONBLANK(OfficeStats[Submarket],[Submarket])
var currDate=MAX([Quarter])
var currQuarter=ROUNDUP(MONTH(currDate)/3, 0)
var temp=FILTER(ALL(OfficeStats),[ClassTier]<>"Owner User"&&[Submarket]=currSubmarket)
return
SUMX(FILTER(temp,ROUNDUP(MONTH([Quarter])/3,0)=currQuarter&&YEAR([Quarter])=YEAR(currDate)),[RBA])

GT of Quarter(Vacant SF) =
var currSubmarket=LASTNONBLANK(OfficeStats[Submarket],[Submarket])
var currDate=MAX([Quarter])
var currQuarter=ROUNDUP(MONTH(currDate)/3, 0)
var temp=FILTER(ALL(OfficeStats),[ClassTier]<>"Owner User"&&[Submarket]=currSubmarket)
return
SUMX(FILTER(temp,ROUNDUP(MONTH([Quarter])/3,0)=currQuarter&&YEAR([Quarter])=YEAR(currDate)),[Vacant SF])

>>Seperately, I need to have Owner User as a line item underneath. Adding the "Investment Grade Inventory" plus Owner User gives us the total.

GT of Quarter(Owner User) =
var currSubmarket=LASTNONBLANK(OfficeStats[Submarket],[Submarket])
var currDate=MAX([Quarter])
var currQuarter=ROUNDUP(MONTH(currDate)/3, 0)
var temp=FILTER(ALL(OfficeStats),[ClassTier]="Owner User"&&[Submarket]=currSubmarket)
return
SUMX(FILTER(temp,ROUNDUP(MONTH([Quarter])/3,0)=currQuarter&&YEAR([Quarter])=YEAR(currDate)),[Vacant SF]+[RBA])

>>If I can get that done, then I'll need to be able to calculate totals of absorption for each class/tier under each submarket for each quarter.

GT of Total = [GT(RBA)]+[GT(Vacant SF)]

>>I also have data from 1999 - 2016, so I'll need to calculate average absorption per tier in specific submarkets, etc.

Avg of RBA(Quarter) =
var currClassTier= LASTNONBLANK(OfficeStats[ClassTier],[ClassTier])
var currSubmarket=LASTNONBLANK(OfficeStats[Submarket],[Submarket])
var currDate=MAX([Quarter])
var currQuarter=ROUNDUP(MONTH(currDate)/3, 0)
var temp=FILTER(ALL(OfficeStats),[ClassTier]=currClassTier&&[Submarket]=currSubmarket)
return
AVERAGEX(FILTER(temp,YEAR([Quarter])=YEAR(currDate)&&ROUNDUP(MONTH([Quarter])/3,0)=currQuarter),[RBA])

Avg of RBA(Year) =
var currClassTier= LASTNONBLANK(OfficeStats[ClassTier],[ClassTier])
var currSubmarket=LASTNONBLANK(OfficeStats[Submarket],[Submarket])
var currDate=MAX([Quarter])
var temp=FILTER(ALL(OfficeStats),[ClassTier]=currClassTier&&[Submarket]=currSubmarket)
return
AVERAGEX(FILTER(temp,YEAR([Quarter])=YEAR(currDate)),[RBA])

Avg of Vacant SF(Quarter) =
var currClassTier= LASTNONBLANK(OfficeStats[ClassTier],[ClassTier])
var currSubmarket=LASTNONBLANK(OfficeStats[Submarket],[Submarket])
var currDate=MAX([Quarter])
var currQuarter=ROUNDUP(MONTH(currDate)/3, 0)
var temp=FILTER(ALL(OfficeStats),[ClassTier]=currClassTier&&[Submarket]=currSubmarket)
return
AVERAGEX(FILTER(temp,YEAR([Quarter])=YEAR(currDate)&&ROUNDUP(MONTH([Quarter])/3,0)=currQuarter),[Vacant SF])

Avg of Vacant SF(Year) =
var currClassTier= LASTNONBLANK(OfficeStats[ClassTier],[ClassTier])
var currSubmarket=LASTNONBLANK(OfficeStats[Submarket],[Submarket])
var currDate=MAX([Quarter])
var temp=FILTER(ALL(OfficeStats),[ClassTier]=currClassTier&&[Submarket]=currSubmarket)
return
AVERAGEX(FILTER(temp,YEAR([Quarter])=YEAR(currDate)),[Vacant SF])

Regards,

Xiaoxin Sheng