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

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