topic Re: Reporting occupancy numbers on a monthly basis in DAX Commands and Tips

Reporting occupancy numbers on a monthly basis

Davebryant1 — Wed, 26 Oct 2022 09:21:35 GMT

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

Re: Reporting occupancy numbers on a monthly basis

johnt75 — Wed, 26 Oct 2022 09:55:33 GMT

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

Re: Reporting occupancy numbers on a monthly basis

Davebryant1 — Wed, 26 Oct 2022 11:33:14 GMT

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

Re: Reporting occupancy numbers on a monthly basis

johnt75 — Wed, 26 Oct 2022 12:34:34 GMT

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

Re: Reporting occupancy numbers on a monthly basis

Davebryant1 — Wed, 26 Oct 2022 14:40:41 GMT

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

Re: Reporting occupancy numbers on a monthly basis

johnt75 — Wed, 26 Oct 2022 15:03:17 GMT

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

Re: Reporting occupancy numbers on a monthly basis

Davebryant1 — Thu, 27 Oct 2022 13:57:44 GMT

Thanks John, just what i needed.

Re: Reporting occupancy numbers on a monthly basis

Davebryant1 — Fri, 28 Oct 2022 09:31:14 GMT

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

Re: Reporting occupancy numbers on a monthly basis

johnt75 — Fri, 28 Oct 2022 09:37:06 GMT

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

Re: Reporting occupancy numbers on a monthly basis

Davebryant1 — Fri, 28 Oct 2022 09:49:32 GMT

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.

Re: Reporting occupancy numbers on a monthly basis

johnt75 — Fri, 28 Oct 2022 10:01:08 GMT

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

Re: Reporting occupancy numbers on a monthly basis

Davebryant1 — Fri, 28 Oct 2022 12:22:34 GMT

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.

Re: Reporting occupancy numbers on a monthly basis

johnt75 — Fri, 28 Oct 2022 12:32:44 GMT

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

Re: Reporting occupancy numbers on a monthly basis

Davebryant1 — Fri, 28 Oct 2022 15:51:07 GMT

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

Re: Reporting occupancy numbers on a monthly basis

Davebryant1 — Mon, 31 Oct 2022 22:24:30 GMT

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?

Re: Reporting occupancy numbers on a monthly basis

johnt75 — Tue, 01 Nov 2022 09:34:48 GMT

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