Forum Discussion

Davebryant1's avatar
Davebryant1
Frequent Visitor
4 years ago
Solved

Reporting occupancy numbers on a monthly basis

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 TypeStart DateEnd Date
Accommodation type 101/04/202215/04/2022
Accommodation type 116/04/2022 
Accommodation type 130/04/2022 
Accommodation type 115/05/202220/05/2022
Accommodation type 101/06/2022 
Accommodation type 129/06/202204/07/2022
Accommodation type 101/09/202229/09/2022
Accommodation type 202/09/2022 
Accommodation type 201/04/2022 
Accommodation type 231/03/202204/04/2022

 

I have a calendar table as follows

 

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

 

This is what im trying to output

 

 AprilMayJuneJulyAugustSeptember
Number of occupants at the end of the month in Accommondation Type 1224333

 

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

 

Thanks

Dave

  • johnt75's avatar
    johnt75
    4 years ago

    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

15 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    4 years ago

    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
  • Davebryant1's avatar
    Davebryant1
    Frequent Visitor
    4 years ago

    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's avatar
      johnt75
      Icon for Super User rankSuper User
      4 years ago

      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's avatar
        Davebryant1
        Frequent Visitor
        4 years ago

        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