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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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 :)

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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