Forum Discussion

nmamm's avatar
nmamm
Frequent Visitor
3 years ago
Solved

metric by date range with multiple sequential observations per category

I have a data set which represents the occupancy of apartment units: 

 

I would like to report on the number of occupied units per day, which I figure should be done in the following way:

for each unitID, get the record with the Max(Filter(Table, Occured Date<Current Date)) then count the records with a status of "Occupied", then plot that data over time against my date table. My date table is linked to the occured date.

 

For example, I would expect the # occupied units:

-as of 5/2/2022 to be 5

-as of 2/1/2023 to be 4 (Unit ID #5 has a more recent record dated 1/1/2023 with a Status = "Occupied - On Notice"

 

 

 

Need Help
  • nmamm's avatar
    nmamm
    3 years ago

    Greg_Deckler I think I got it..

    % Mkt Occ = 
VAR tmpunitStatus = ADDCOLUMNS(unitstatus,"Effective Date",IF(ISBLANK([End]),TODAY(),[End]))
VAR tmpTable =  
    FILTER(
        GENERATE(
               tmpunitStatus,
            CalendarTable
        ),
    
       and( And([Date] <= [End], [Date] >= [Start]),[Status]="Occupied")
    )

RETURN COUNTROWS(tmpTable)/DISTINCTCOUNT([UnitID])

     

    I realized part of the issue was that the fact table was linked to the date table, which was forcing unwanted behavior. When I plot this by day, it produces expected results. However, if you try summarize the data by any other time dimension (particularly because it is rows of days that is used in the generate function) then I get sums of daily amounts (of course) which I don't want. Therefore, inspired by this I came up with the following solution:

    % Mkt Occ (Current) = 
VAR tmpTable =  
    FILTER(
unitstatus,
       and( And(max(CalendarTable[Date]) <= [End], max(CalendarTable[Date]) >= [Start]),[Status]="Occupied")
    )

RETURN COUNTROWS(tmpTable)/DISTINCTCOUNT(unitstatus[UnitID])

    Inspired by your suggested solution, dynamically filter the fact table based on the max date of the calendar table, which itself is dynamic based on the time dimension being plotted. 

7 Replies