Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Good evening everyone!
I'm currently converting an excel spreadsheet to Access that I have linked to Power BI. I need help understanding how to convert the data so I can calculate differences of market data between months and quarters. I've been using Excel to calculate market data for the office commercial real estate market. I'm super new to Power BI and I think it's going to be amazing, but definitely need some help, please.
The information that I get is from a paid monthly subscription, and I have to export it to excel and then connect to Power BI. Below is the data that I receive each month:
There are 11 submarkets that we track the stats for our office market. For example, those 11 submarkets total 160 million SF. We also get vacant SF. From there, we have to calculate "Occupied SF," which is the total RSF minus the vacant SF.
This is an import aspect because each month the most important stat we watch is absorption. Absorption is the change (positive or negative) in occupied SF. So I need to be able to track the absorption pretty much from any given period that I could use, so incorporating the slicer is perfect. My problem is that I don't know
Specific questions:
Thank everyone in advance for the time you've given me by reading this and/or possibly helping 🙂
Below is what I've put together via an image.
Solved! Go to Solution.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |