Reporting occupancy numbers on a monthly basis

[Davebryant1]

Hi,

Im looking for some help reporting tenancy occupancy numbers on the last day of the month for specific accommodation. This will include tenancys that have closed and are still open. Below is a sample of the data

Table 1

Accommodation Type	Start Date	End Date
Accommodation type 1	01/04/2022	15/04/2022
Accommodation type 1	16/04/2022
Accommodation type 1	30/04/2022
Accommodation type 1	15/05/2022	20/05/2022
Accommodation type 1	01/06/2022
Accommodation type 1	29/06/2022	04/07/2022
Accommodation type 1	01/09/2022	29/09/2022
Accommodation type 2	02/09/2022
Accommodation type 2	01/04/2022
Accommodation type 2	31/03/2022	04/04/2022

I have a calendar table as follows

Date	Month
01-Apr-22	Apr
02-Apr-22	Apr
03-Apr-22	Apr
04-Apr-22	Apr
05-Apr-22	Apr
06-Apr-22	Apr
07-Apr-22	Apr
08-Apr-22	Apr
09-Apr-22	Apr
10-Apr-22	Apr

This is what im trying to output

	April	May	June	July	August	September
Number of occupants at the end of the month in Accommondation Type 1	2	2	4	3	3	3

I've tried various code but cant seem to crack it. Any advice or pointers would be much appreciated.

Thanks

Dave

[Davebryant1]

Thanks for the speedy response John. 

I've had a go but not getting the results im expecting, this is your code with my real table names

Occupancy =

VAR ReferenceDate =

    MAX ( 'dimDate (start Date)'[Date] )

VAR Result =

    CALCULATE (

        COUNTROWS ( Tenancies ),

        'Tenancies'[Tenancy Start Date] <= ReferenceDate

            && (

                ISBLANK ( Tenancies [Tenancy End Date] )

                    || Tenancies[Tenancy End Date] > ReferenceDate

            )

    )

RETURN

    Result

These are the results im gettng but, i should be getting results in the region of 500 each month. 

My calendar table (dimDate (start Date)) is joined to the Tenancies table on the tenancy start date field. Could this be causing issues?

Many Thanks

[johnt75]

Yes, the filter on date will be affecting the results. Try

Occupancy =
VAR ReferenceDate =
    MAX ( 'dimDate (start Date)'[Date] )
VAR Result =
    CALCULATE (
        COUNTROWS ( Tenancies ),
        REMOVEFILTERS ( 'dimDate (start Date)' ),
        'Tenancies'[Tenancy Start Date] <= ReferenceDate
            && (
                ISBLANK ( Tenancies[Tenancy End Date] )
                    || Tenancies[Tenancy End Date] > ReferenceDate
            )
    )
RETURN
    Result

[Davebryant1]

Thanks John, i think i've cracked it.

I had to create a new collumn in the date table that shows the last day of the month and amend your code to

Occupancy1 =

VAR ReferenceDate =

    MAX ( 'dimDate (start Date)'[Endofmonthdate] )

VAR Result =

    CALCULATE (

        COUNTROWS ( Tenancies ),

        REMOVEFILTERS ( 'dimDate (start Date)' ),

        'Tenancies'[Tenancy Start Date] <= ReferenceDate

            && (

                ISBLANK ( Tenancies[Tenancy End Date] )

                    || Tenancies[Tenancy End Date] > ReferenceDate

            )

    )

RETURN

    Result

My Final question now is how do i add a filter into it? As per my origional post i only want to count those rows that are "Accommodation type 1"

Many Thanks

[johnt75 · Accepted solution]

You can add another filter into the CALCULATE

Occupancy1 =
VAR ReferenceDate =
    MAX ( 'dimDate (start Date)'[Endofmonthdate] )
VAR Result =
    CALCULATE (
        COUNTROWS ( Tenancies ),
        REMOVEFILTERS ( 'dimDate (start Date)' ),
        'Tenancies'[Tenancy Start Date] <= ReferenceDate
            && (
                ISBLANK ( Tenancies[Tenancy End Date] )
                    || Tenancies[Tenancy End Date] > ReferenceDate
            ),
        'Tenancies'[Accomodation type] = "Accomodation type 1"
    )
RETURN
    Result

[Davebryant1]

Hi @johnt75 ,

Im after some more help with adapting the code you've already provided if possible? If you want me to post as a new thread let me know...

I now need to report on the same paramaters as before but i need to know as at the end of each month how many tenancys had been active for 4 weeks or more.

I have had an inital go as below but the number of days in tenancy is calculating the overall number and not as at the end of each month. 

Tenancy Over 4 weeks = 
VAR ReferenceDate =
    MAX ( 'dimDate (start Date)'[MonthDate] )
VAR Result =
    CALCULATE (
        COUNTROWS (Tenancies ),
        REMOVEFILTERS ( 'dimDate (start Date)' ),
        Tenancies[Tenancy Start Date] <= ReferenceDate
            && (
                ISBLANK ( Tenancies[Tenancy End Date] )
                    || Tenancies[Tenancy End Date] > ReferenceDate
            ),
        Properties[property type] = "Type1",
        Tenancies[Number of days in tenancy] >28
        
    )
RETURN
    Result

Thanks

[johnt75]

what's the code for the Number of days in tenancy?

[Davebryant1]

That collumn is a system generated field that produces the number of days the Tenancy is/was active. It perorms 2 calulations end date - start date and if end date = blank, Todays date-Start date.

[johnt75]

That's where the problem is. You don't want to calculate it as at today, you want to calculate it as at the end of the month in question.

Try

Tenancy Over 4 weeks =
VAR ReferenceDate =
    MAX ( 'dimDate (start Date)'[MonthDate] )
VAR Result =
    CALCULATE (
        COUNTROWS (
            KEEPFILTERS (
                FILTER (
                    Tenancies,
                    DATEDIFF (
                        Tenancies[Tenancy Start Date],
                        COALESCE ( Tenancies[Tenancy End Date], ReferenceDate ),
                        DAY
                    ) > 28
                )
            )
        ),
        REMOVEFILTERS ( 'dimDate (start Date)' ),
        Tenancies[Tenancy Start Date] <= ReferenceDate
            && (
                ISBLANK ( Tenancies[Tenancy End Date] )
                    || Tenancies[Tenancy End Date] > ReferenceDate
            ),
        Properties[property type] = "Type1"
    )
RETURN
    Result

[Davebryant1]

Hi John, 

I've tried to get the code working but still strugggling. I think its failing on those tenancies that have an end date. Its calculating those with an end date as end date - start date at each date point rather than weekly date - start date. E.g

Case ID	Tenancy Start Date	Tenancy End Date	As at 22/08/2022	Days Tenancy Open as at 22/8
227830	19/08/2022		22/08/2022	3
237823	18/08/2022	17/10/2022	22/08/2022	4
211285	17/06/2022		22/08/2022	66

based on the above data as at 22/8 it should only count ID211285 but its also counting ID237823.

Here's my latest code, i've changed it to a weekly view from monthly now.

Tenancy Over 4 weeks = 
VAR ReferenceDate =
    MAX ( 'dimDate (start Date)'[WeekDate])
VAR Result =
    CALCULATE (
        COUNTROWS (
            FILTER (
                KEEPFILTERS ( Tenancies ),
                DATEDIFF (
                   Tenancies[Tenancy Start Date],
                    COALESCE (Tenancies[Tenancy End Date], ReferenceDate ),
                    DAY
                ) > 28
            )
        ),
        REMOVEFILTERS ( 'dimDate (start Date)' ),
        Tenancies[Tenancy Start Date]<= ReferenceDate
            && (
                ISBLANK (Tenancies[Tenancy End Date])
                    || Tenancies[Tenancy End Date] > ReferenceDate
            ),
        Properties[property type] = "Type1"
        
    )
RETURN
    Result

Any ideas?

[johnt75]

Try

Tenancy Over 4 weeks = 
VAR ReferenceDate =
    MAX ( 'dimDate (start Date)'[WeekDate])
VAR Result =
    CALCULATE (
        COUNTROWS (
            FILTER (
                KEEPFILTERS ( Tenancies ),
                DATEDIFF (
                   Tenancies[Tenancy Start Date],
                    MINX( {Tenancies[Tenancy End Date], ReferenceDate}, [Value] ),
                    DAY
                ) > 28
            )
        ),
        REMOVEFILTERS ( 'dimDate (start Date)' ),
        Tenancies[Tenancy Start Date]<= ReferenceDate
            && (
                ISBLANK (Tenancies[Tenancy End Date])
                    || Tenancies[Tenancy End Date] > ReferenceDate
            ),
        Properties[property type] = "Type1"
        
    )
RETURN
    Result

[Davebryant1]

Thanks John, tried that code but am getting the error below:

KEEPFILTERS function can only be used as a top level filter argument of CALCULATE and CALCULATETABLE or with a table argument of a function performing a table scan.

[johnt75]

I always get them the wrong way round. Try

Tenancy Over 4 weeks =
VAR ReferenceDate =
    MAX ( 'dimDate (start Date)'[MonthDate] )
VAR Result =
    CALCULATE (
        COUNTROWS (
            FILTER (
                KEEPFILTERS ( Tenancies ),
                DATEDIFF (
                    Tenancies[Tenancy Start Date],
                    COALESCE ( Tenancies[Tenancy End Date], ReferenceDate ),
                    DAY
                ) > 28
            )
        ),
        REMOVEFILTERS ( 'dimDate (start Date)' ),
        Tenancies[Tenancy Start Date] <= ReferenceDate
            && (
                ISBLANK ( Tenancies[Tenancy End Date] )
                    || Tenancies[Tenancy End Date] > ReferenceDate
            ),
        Properties[property type] = "Type1"
    )
RETURN
    Result

[Davebryant1]

Thanks John, its returning figures not but not what i am expecting. I need to investigate more to understand whats going on.

Thanks for your assistance

[Davebryant1]

Thanks John, just what i needed.

[johnt75]

You could create a measure like

Occupancy =
VAR ReferenceDate =
    MAX ( 'Date'[Date] )
VAR Result =
    CALCULATE (
        COUNTROWS ( 'Table 1' ),
        'Table 1'[Start date] <= ReferenceDate
            && (
                ISBLANK ( 'Table 1'[End date] )
                    || 'Table 1'[End date] > ReferenceDate
            )
    )
RETURN
    Result