metric by date range with multiple sequential observations per category

[nmamm]

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"

[nmamm]

@Greg_Deckler thanks for your reply! I think you are on to something. Using the periodic billing file as an example, the complexity I have is that "Customer A" is repeated, and I only want to count some of their orders, and not others.

[Greg_Deckler]

@nmamm Can you provide sample data to work with?

[Greg_Deckler]

@nmamm Can you provide sample data to work with?

[nmamm]

@Greg_Deckler 

Where Start and End is equivalent to the example columns begin date and until date

UnitID	Status	Occured	Start	End
1	Occupied	12/1/2021	12/1/2021	11/30/2022
2	Occupied	12/1/2021	12/1/2021	11/30/2022
3	Occupied	12/1/2021	12/1/2021	11/30/2022
4	Occupied	12/1/2021	12/1/2021	11/30/2022
1	Occupied	12/1/2022	12/1/2022
2	Occupied	12/1/2022	12/1/2022
3	Occupied	12/1/2022	12/1/2022
4	Occupied	12/1/2022	12/1/2022
5	Occupied	4/1/2021	4/1/2021	3/31/2022
5	Occupied	4/1/2022	4/1/2022	12/31/2022
5	Occupied - On Notice	1/1/2023	1/1/2023

[nmamm · Accepted solution]

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

[Greg_Deckler]

@nmamm Outstanding!

[Greg_Deckler]

@nmamm 
Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365