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."
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
WOO HOO! This is amazing! Thank you so much Anonymous for taking the time to dig into my issue.
I'm very impressed!:smileytongue:
For some reason I'm not able to get the quarterly absorption numbers to work however. Here's the link for the updated file for anyone else that wants to be impressed with Anonymous fine work! Or if you can get the quarterly absorption numbers working, that would be great too!
https://www.dropbox.com/s/xy1fmoqnd983hs7/Dashboard%20-%2012.7.16.pbix?dl=0
And thanks to JanMulkens for chiming in as well. This forum is AWESOME!
Thanks again,
Paul
Hi Anonymous,
The formula of Absorption(Quarter) seems well, but I find that your records' date(quarter column) which in the same quarter are same. So the formula can't use min date and max date to filter data.
For example:
Records' date are between 1/1 to 3/1, my formula can get the min date 1/1, max date 3/30, then summary the same date's value and get the diff.(summary max date(3/31) - summary min date(1/1))
In your side, min date and max date are 1/1, so the result = summary max date(1/1) - summary min date(1/1) = 0Regards,
Xiaoxin Sheng
Hi Everyone for all the kind help this week! I've been up all night trying to get this work so I could count quarterly statistics specifically for absorption levels (RBA-Vacant SF).
The issue was that my table, and measure provided so generously by Anonymous were only in quarters, so I need them to be changed to monthly calendars.
I've tried to get it working in Power BI, but to no avail, so I'm curious if there's a bug in the code. Here is my Dropbox link if anyone wants to take a shot at it
https://www.dropbox.com/sh/5tprfb1zq76560j/AAApxYNvZEB7SdJ6mrmzNhlVa?dl=0
- it has sample data I'm using from Excel and the most recently couple days of Power BI applications.
Thank you!
Let me know if you any issues OR (EARLY CHRISTMAS PRESENT) and you get it working :)