Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Calculating Market Statistics - Difference from Time Periods

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.

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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

14 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      9 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        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

  • dkay84_PowerBI's avatar
    dkay84_PowerBI
    Microsoft Employee
    9 years ago
    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.
  • JanMulkens's avatar
    JanMulkens
    Advocate I
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      9 years ago

       

      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

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        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