How to count occupation Dates

[JohnSpartan]

Dear All,

First time posting !!

I´m preparing a report and I´m really stuck with something.

I have a hotel with some room and I have to calculate how many days the room has been ocupied during a specify period (IE. January)

Room	Date From	To
123	26/12/2016	06/01/2017
123	13/01/2017	21/01/2017
123	27/01/2017	06/02/2017

Somebody can help me ??

Thanks in advance

[v-shex-msft · Accepted solution]

Hi @JohnSpartan,

You can refer to below sample to achieve your requirement.

1. Create a calendar table based on record table.

CALENDAR = CALENDAR(FIRSTDATE(Records[Date From]),LASTDATE(Records[To]))

2. Write a measure to calculate the occupation date count.

Dynamic Count = 
VAR current_Date =
    MAX ( 'CALENDAR'[Date] )
VAR stare_date =
    DATE ( YEAR ( current_Date ), MONTH ( current_Date ), 1 )
VAR end_date =
    DATE ( YEAR ( current_Date ), MONTH ( current_Date ) + 1, 1 )
        - 1
VAR filtered =
    FILTER (
        ALL ( Records ),
        CONTAINS (
            ADDCOLUMNS (
                CALENDAR ( [Date From], [To] ),
                "YearMonth", FORMAT ( [Date], "mmm/yyyy" )
            ),
            [YearMonth], FORMAT ( current_Date, "mmm/yyyy" )
        )
    )
RETURN
    SUMX (
        ADDCOLUMNS (
            ADDCOLUMNS (
                filtered,
                "StartDat", IF ( [Date From] <= stare_date, stare_date, [Date From] ),
                "EndDate", IF ( [To] >= end_date, end_date, [To] )
            ),
            "Diff", DATEDIFF ( [StartDat], [EndDate], DAY )
        ),
        [Diff]
    )

3. Use measure and calendar date to create visuals.

Comment:

VAR stare_date =
DATE ( YEAR ( current_Date ), MONTH ( current_Date ), 1 )
VAR end_date =
DATE ( YEAR ( current_Date ), MONTH ( current_Date ) + 1, 1 )
- 1
find out the startdate and enddate of current month.

VAR filtered =
FILTER (
ALL ( Records ),
CONTAINS (
ADDCOLUMNS (
CALENDAR ( [Date From], [To] ),
"YearMonth", FORMAT ( [Date], "mmm/yyyy" )
),
[YearMonth], FORMAT ( current_Date, "mmm/yyyy" )
)
)
filter related records based on year month of current date.

ADDCOLUMNS (
ADDCOLUMNS (
filtered,
"StartDat", IF ( [Date From] <= stare_date, stare_date, [Date From] ),
"EndDate", IF ( [To] >= end_date, end_date, [To] )
),
"Diff", DATEDIFF ( [StartDat], [EndDate], DAY )
)
Dynamic compare the current record date with variable start_date/end_date, add columns to store these correct date and calculate the date diff.

Notice: I attach the pbix file below.

Regards,

Xiaoxin Sheng

[Ashish_Mathur · Accepted solution]

Hi,

You may refer to my solution here.

Hope this helps.

[Vassilium]

hi All 

i revive this thread just to ask how we can show the occupancy as percentage vs month/quarter/year. 

i actually try to divide the number of days that the room is booked with the number of available days in each given period. 

i tryied with eomonth function but with no chance

Is there anyway to do so? 

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[v-shex-msft · Accepted solution]

Hi @JohnSpartan,

You can refer to below sample to achieve your requirement.

1. Create a calendar table based on record table.

CALENDAR = CALENDAR(FIRSTDATE(Records[Date From]),LASTDATE(Records[To]))

2. Write a measure to calculate the occupation date count.

Dynamic Count = 
VAR current_Date =
    MAX ( 'CALENDAR'[Date] )
VAR stare_date =
    DATE ( YEAR ( current_Date ), MONTH ( current_Date ), 1 )
VAR end_date =
    DATE ( YEAR ( current_Date ), MONTH ( current_Date ) + 1, 1 )
        - 1
VAR filtered =
    FILTER (
        ALL ( Records ),
        CONTAINS (
            ADDCOLUMNS (
                CALENDAR ( [Date From], [To] ),
                "YearMonth", FORMAT ( [Date], "mmm/yyyy" )
            ),
            [YearMonth], FORMAT ( current_Date, "mmm/yyyy" )
        )
    )
RETURN
    SUMX (
        ADDCOLUMNS (
            ADDCOLUMNS (
                filtered,
                "StartDat", IF ( [Date From] <= stare_date, stare_date, [Date From] ),
                "EndDate", IF ( [To] >= end_date, end_date, [To] )
            ),
            "Diff", DATEDIFF ( [StartDat], [EndDate], DAY )
        ),
        [Diff]
    )

3. Use measure and calendar date to create visuals.

Comment:

VAR stare_date =
DATE ( YEAR ( current_Date ), MONTH ( current_Date ), 1 )
VAR end_date =
DATE ( YEAR ( current_Date ), MONTH ( current_Date ) + 1, 1 )
- 1
find out the startdate and enddate of current month.

VAR filtered =
FILTER (
ALL ( Records ),
CONTAINS (
ADDCOLUMNS (
CALENDAR ( [Date From], [To] ),
"YearMonth", FORMAT ( [Date], "mmm/yyyy" )
),
[YearMonth], FORMAT ( current_Date, "mmm/yyyy" )
)
)
filter related records based on year month of current date.

ADDCOLUMNS (
ADDCOLUMNS (
filtered,
"StartDat", IF ( [Date From] <= stare_date, stare_date, [Date From] ),
"EndDate", IF ( [To] >= end_date, end_date, [To] )
),
"Diff", DATEDIFF ( [StartDat], [EndDate], DAY )
)
Dynamic compare the current record date with variable start_date/end_date, add columns to store these correct date and calculate the date diff.

Notice: I attach the pbix file below.

Regards,

Xiaoxin Sheng