Forum Discussion
Calculating Market Statistics - Difference from Time Periods
Hi Anonymous,
You can use below formula to get the "Absorption" of different date ranges:
Absorption(Quarter) =
var currClassTier= LASTNONBLANK(OfficeStats[ClassTier],[ClassTier])
var currSubmarket=LASTNONBLANK(OfficeStats[Submarket],[Submarket])
var currQuarter=ROUNDUP(MONTH(MAX([Quarter]))/3, 0)
var temp=FILTER(ALL(OfficeStats),[ClassTier]=currClassTier&&[Submarket]=currSubmarket&&YEAR([Quarter])=YEAR(MAX([Quarter]))&&ROUNDUP(MONTH([Quarter])/3, 0)=ROUNDUP(MONTH(MAX([Quarter]))/3, 0))
return
SUMX(FILTER(temp,[Quarter]=MAXX(temp,[Quarter])),[Occupied SF]) -SUMX(FILTER(temp,[Quarter]=MINX(temp,[Quarter])),[Occupied SF])Absorption(Year) =
var currClassTier= LASTNONBLANK(OfficeStats[ClassTier],[ClassTier])
var currSubmarket=LASTNONBLANK(OfficeStats[Submarket],[Submarket])
var temp=FILTER(ALL(OfficeStats),[ClassTier]=currClassTier&&[Submarket]=currSubmarket&&YEAR([Quarter])=YEAR(MAX([Quarter])))
return
SUMX(FILTER(temp,[Quarter]=MAXX(temp,[Quarter])),[Occupied SF]) -SUMX(FILTER(temp,[Quarter]=MINX(temp,[Quarter])),[Occupied SF])
Absorption(All) =
var currClassTier= LASTNONBLANK(OfficeStats[ClassTier],[ClassTier])
var currSubmarket=LASTNONBLANK(OfficeStats[Submarket],[Submarket])
var temp=FILTER(ALL(OfficeStats),[ClassTier]=currClassTier&&[Submarket]=currSubmarket)
return
SUMX(FILTER(temp,[Quarter]=MAXX(temp,[Quarter])),[Occupied SF]) -SUMX(FILTER(temp,[Quarter]=MINX(temp,[Quarter])),[Occupied SF])
Regards,Xiaoxin Sheng
Xiaoxin,
This is fantastic! Thanks a ton for your help! Is the absorption column working on your side? For me, it's just showing up as all "0's."
Wow, came to check back and was amazed by the great work by Anonymous
Absorption does show values but only in the overall total.
I'll take some time tomorrow to go over Anonymous's solution.
Might just be a filtering issue.