Calculating Market Statistics - Difference from Time Periods

[Anonymous]

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:

• Submarket - 11 submarkets
• Class / Tier - Class A Tier 1, Class A Tier 2, Class A Tier 3, Class B, Owner User
• Rentable Square Feet ("RSF")
• Vacant Square Feet ("SF")

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:

1. How to calculate difference from previous periods (quarters, months, etc.); this would be for supply and absorption
   1. I also need to be able to track year over year changes, or absorption to date if possible
2. For each quarter, and under each submarket, I need to be able to add up Class A Tier, Class A Tier 2, and Class B (grouped together - AKA "Investment Grade Inventory"). Seperately, I need to have Owner User as a line item underneath. Adding the "Investment Grade Inventory" plus Owner User gives us the total.
3. If I can get that done, then I'll need to be able to calculate totals of absorption for each class/tier under each submarket for each quarter.
4. I also have data from 1999 - 2016, so I'll need to calculate average absorption per tier in specific submarkets, etc.

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.

[v-shex-msft]

Hi @Anonymous,

You can take a look at below measures formula :

>>How to calculate difference from previous periods (quarters, months, etc.); this would be for supply and absorption

Diff of Previous Quarter(RBA) =
var currClassTier= LASTNONBLANK(OfficeStats[ClassTier],[ClassTier])
var currSubmarket=LASTNONBLANK(OfficeStats[Submarket],[Submarket])
var currDate=MAX([Quarter])
var currQuarter=ROUNDUP(MONTH(currDate)/3, 0)
var temp=FILTER(ALL(OfficeStats),[ClassTier]=currClassTier&&[Submarket]=currSubmarket)
return
SUMX(FILTER(temp,ROUNDUP(MONTH([Quarter])/3,0)=currQuarter&&YEAR([Quarter])=YEAR(currDate)),[RBA])-
if(currQuarter>1,
SUMX(FILTER(temp,YEAR([Quarter])=YEAR(currDate)&&ROUNDUP(MONTH([Quarter])/3,0)=currQuarter-1),[RBA]),
SUMX(FILTER(temp,YEAR([Quarter])=YEAR(currDate)-1&&ROUNDUP(MONTH([Quarter])/3,0)=4),[RBA]))

Diff of Previous Quarter(Vacant SF) =
var currClassTier= LASTNONBLANK(OfficeStats[ClassTier],[ClassTier])
var currSubmarket=LASTNONBLANK(OfficeStats[Submarket],[Submarket])
var currDate=MAX([Quarter])
var currQuarter=ROUNDUP(MONTH(currDate)/3, 0)
var temp=FILTER(ALL(OfficeStats),[ClassTier]=currClassTier&&[Submarket]=currSubmarket)
return
SUMX(FILTER(temp,ROUNDUP(MONTH([Quarter])/3,0)=currQuarter&&YEAR([Quarter])=YEAR(currDate)),[Vacant SF])-
if(currQuarter>1,
SUMX(FILTER(temp,YEAR([Quarter])=YEAR(currDate)&&ROUNDUP(MONTH([Quarter])/3,0)=currQuarter-1),[Vacant SF]),
SUMX(FILTER(temp,YEAR([Quarter])=YEAR(currDate)-1&&ROUNDUP(MONTH([Quarter])/3,0)=4),[Vacant SF]))

>>I also need to be able to track year over year changes, or absorption to date if possible

Diff of Previous Year(RBA) =
var currClassTier= LASTNONBLANK(OfficeStats[ClassTier],[ClassTier])
var currSubmarket=LASTNONBLANK(OfficeStats[Submarket],[Submarket])
var currDate=MAX([Quarter])
var temp=FILTER(ALL(OfficeStats),[ClassTier]=currClassTier&&[Submarket]=currSubmarket)
return
SUMX(FILTER(temp,YEAR([Quarter])=YEAR(currDate)),[RBA])-SUMX(FILTER(temp,YEAR([Quarter])=YEAR(currDate)-1),[RBA])

Diff of Previous Year(Vacant SF) =
var currClassTier= LASTNONBLANK(OfficeStats[ClassTier],[ClassTier])
var currSubmarket=LASTNONBLANK(OfficeStats[Submarket],[Submarket])
var currDate=MAX([Quarter])
var temp=FILTER(ALL(OfficeStats),[ClassTier]=currClassTier&&[Submarket]=currSubmarket)
return
SUMX(FILTER(temp,YEAR([Quarter])=YEAR(currDate)),[Vacant SF])-SUMX(FILTER(temp,YEAR([Quarter])=YEAR(currDate)-1),[Vacant SF])

>>For each quarter, and under each submarket, I need to be able to add up Class A Tier, Class A Tier 2, and Class B (grouped together - AKA "Investment Grade Inventory").

GT of Quarter(RBA) =
var currSubmarket=LASTNONBLANK(OfficeStats[Submarket],[Submarket])
var currDate=MAX([Quarter])
var currQuarter=ROUNDUP(MONTH(currDate)/3, 0)
var temp=FILTER(ALL(OfficeStats),[ClassTier]<>"Owner User"&&[Submarket]=currSubmarket)
return
SUMX(FILTER(temp,ROUNDUP(MONTH([Quarter])/3,0)=currQuarter&&YEAR([Quarter])=YEAR(currDate)),[RBA])

GT of Quarter(Vacant SF) =
var currSubmarket=LASTNONBLANK(OfficeStats[Submarket],[Submarket])
var currDate=MAX([Quarter])
var currQuarter=ROUNDUP(MONTH(currDate)/3, 0)
var temp=FILTER(ALL(OfficeStats),[ClassTier]<>"Owner User"&&[Submarket]=currSubmarket)
return
SUMX(FILTER(temp,ROUNDUP(MONTH([Quarter])/3,0)=currQuarter&&YEAR([Quarter])=YEAR(currDate)),[Vacant SF])

>>Seperately, I need to have Owner User as a line item underneath. Adding the "Investment Grade Inventory" plus Owner User gives us the total.

GT of Quarter(Owner User) =
var currSubmarket=LASTNONBLANK(OfficeStats[Submarket],[Submarket])
var currDate=MAX([Quarter])
var currQuarter=ROUNDUP(MONTH(currDate)/3, 0)
var temp=FILTER(ALL(OfficeStats),[ClassTier]="Owner User"&&[Submarket]=currSubmarket)
return
SUMX(FILTER(temp,ROUNDUP(MONTH([Quarter])/3,0)=currQuarter&&YEAR([Quarter])=YEAR(currDate)),[Vacant SF]+[RBA])

>>If I can get that done, then I'll need to be able to calculate totals of absorption for each class/tier under each submarket for each quarter.

GT of Total = [GT(RBA)]+[GT(Vacant SF)]

>>I also have data from 1999 - 2016, so I'll need to calculate average absorption per tier in specific submarkets, etc.

Avg of RBA(Quarter) =
var currClassTier= LASTNONBLANK(OfficeStats[ClassTier],[ClassTier])
var currSubmarket=LASTNONBLANK(OfficeStats[Submarket],[Submarket])
var currDate=MAX([Quarter])
var currQuarter=ROUNDUP(MONTH(currDate)/3, 0)
var temp=FILTER(ALL(OfficeStats),[ClassTier]=currClassTier&&[Submarket]=currSubmarket)
return
AVERAGEX(FILTER(temp,YEAR([Quarter])=YEAR(currDate)&&ROUNDUP(MONTH([Quarter])/3,0)=currQuarter),[RBA])

Avg of RBA(Year) =
var currClassTier= LASTNONBLANK(OfficeStats[ClassTier],[ClassTier])
var currSubmarket=LASTNONBLANK(OfficeStats[Submarket],[Submarket])
var currDate=MAX([Quarter])
var temp=FILTER(ALL(OfficeStats),[ClassTier]=currClassTier&&[Submarket]=currSubmarket)
return
AVERAGEX(FILTER(temp,YEAR([Quarter])=YEAR(currDate)),[RBA])

Avg of Vacant SF(Quarter) =
var currClassTier= LASTNONBLANK(OfficeStats[ClassTier],[ClassTier])
var currSubmarket=LASTNONBLANK(OfficeStats[Submarket],[Submarket])
var currDate=MAX([Quarter])
var currQuarter=ROUNDUP(MONTH(currDate)/3, 0)
var temp=FILTER(ALL(OfficeStats),[ClassTier]=currClassTier&&[Submarket]=currSubmarket)
return
AVERAGEX(FILTER(temp,YEAR([Quarter])=YEAR(currDate)&&ROUNDUP(MONTH([Quarter])/3,0)=currQuarter),[Vacant SF])

Avg of Vacant SF(Year) =
var currClassTier= LASTNONBLANK(OfficeStats[ClassTier],[ClassTier])
var currSubmarket=LASTNONBLANK(OfficeStats[Submarket],[Submarket])
var currDate=MAX([Quarter])
var temp=FILTER(ALL(OfficeStats),[ClassTier]=currClassTier&&[Submarket]=currSubmarket)
return
AVERAGEX(FILTER(temp,YEAR([Quarter])=YEAR(currDate)),[Vacant SF])

Regards,

Xiaoxin Sheng

[Anonymous]

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."

[v-shex-msft · Accepted 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

[Anonymous]

WOO HOO! This is amazing! Thank you so much @v-shex-msft for taking the time to dig into my issue.

I'm very impressed!

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 @v-shex-msft 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

[v-shex-msft]

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) = 0

Regards,

Xiaoxin Sheng

[Anonymous]

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 @v-shex-msft 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 🙂

[Anonymous]

Hi Everyone - I'm still really looking for help if possible on this last piece to be able to calculate the absorption by month, not just in the auto-sum selection from the matrix.

Any help would be appreciated since this is due for me really soon. 🙂

Here is the link to the files via Dropbox

https://www.dropbox.com/sh/5tprfb1zq76560j/AAApxYNvZEB7SdJ6mrmzNhlVa?dl=0

Thank you,

Paul

[JanMulkens]

Wow, came to check back and was amazed by the great work by @v-shex-msft

Absorption does show values but only in the overall total.

I'll take some time tomorrow to go over @v-shex-msft's solution.

Might just be a filtering issue.

[JanMulkens]

Hi @Anonymous, This is a very interesting question.

Could you provide some sample data as @dkay84_PowerBI requested?

It would enable us to help you create the requested measures by referring to the correct tables/fields and DAX functions.

Regards,

Jan Mulkens

[Anonymous]

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

[v-shex-msft]

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

[Anonymous]

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

[Anonymous]

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?

[dkay84_PowerBI]

Once you load your data and configure your tables, DAX has a lot of functions for calculating YTD or QTD metrics. A web search for DAX time intelligence functions will get you started.

If you need help with a specific function or data modeling task, please provide some sample data for us to play with.