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
Jan - thanks a ton for looking into this for me! Please see below!
the Table imported into Power BISample Data Uploaded - Page 1Sample Data Uploaded - Page 2Sample Data Uploaded - Page 3Sample Data Uploaded - Page 4
Sample Data Uploaded - Page 4
Sample Data Uploaded - Page 3
Sample Data Uploaded - Page 2
Sample Data Uploaded - Page 1
This is as far as I can get with my Matrix
Here is the data as it is in a table in Power BI
Hi Anonymous,
You can take a look at below formula: Summary table and get the previous month value.
Summary table =
var temp=SUMMARIZE(Sheet1,Sheet1[Date Year Month],Sheet1[Submarket],Sheet1[ClassTier],"Total RBA",SUM(Sheet1[RBA]),"Total Vacant SF",SUM(Sheet1[Vacant SF]),"Total Sublease SF",SUM(Sheet1[Sublease SF]),"Total Occupied SF",SUM(Sheet1[Occupied SF]))
return
ADDCOLUMNS(temp,
"Total RBA Of Pervious Month",SUMX(FILTER(temp,MONTH(DATEVALUE([Date Year Month]))=MONTH(EARLIER([Date Year Month])) -1&&[Submarket]=EARLIER([Submarket])&&[ClassTier]=EARLIER([ClassTier])),[Total RBA]),
"Total Sublease SF Of Pervious Month",SUMX(FILTER(temp,MONTH(DATEVALUE([Date Year Month]))=MONTH(EARLIER([Date Year Month])) -1&&[Submarket]=EARLIER([Submarket])&&[ClassTier]=EARLIER([ClassTier])),[Total Sublease SF]),
"Total Vacant SF Of Pervious Month",SUMX(FILTER(temp,MONTH(DATEVALUE([Date Year Month]))=MONTH(EARLIER([Date Year Month])) -1&&[Submarket]=EARLIER([Submarket])&&[ClassTier]=EARLIER([ClassTier])),[Total Vacant SF]),
"Total Occupied SF Of Pervious Month",SUMX(FILTER(temp,MONTH(DATEVALUE([Date Year Month]))=MONTH(EARLIER([Date Year Month])) -1&&[Submarket]=EARLIER([Submarket])&&[ClassTier]=EARLIER([ClassTier])),[Total Occupied SF]))
In addition, can you share a pbix file with some sample data to test?
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!
PaulI'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?