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,
I'm not able to attach the Power BI file on this forum, so here is the Dropbox link:
https://www.dropbox.com/s/0tvnzzrcewbv5su/Dashboard%20-%2012.6.16.pbix?dl=0
Please let me know if there are any issues getting the file.
Thanks so much for your help!
Paul
I'm also entering the formula you provided and substituing Sheet1 for OfficeStats - I assume that's the right way?
Once it's entered, I'm getting an error message at the bottom: "Token Eof expected." Any thoughts on that?