Need help with formula: Count the number of dates that can be found in a timespan

[TestNutzer]

Hello,

For a hotel I want to visualize the occupancy per day, for example in a line chart.

I have a datedimension table with a datekey-column containing the time interval of all dates between the checkin dates and checkout dates from table1.

To realize the visualization I thought I could create a new column in the datedimension table where I can count how often every Date of the Date Key can be found in the space of all the checkin and checkout dates.

Like Count the number IF datekey is bigger or equal as checkin date AND datekey is less or equal than checkoutdate. Then I could visualize this quantity in a line chart with days as timeframe.

The problem is that I created a new column and tried to create a formula but I couldn’t use the RELATED function to use the checkin and checkout dates for the fomula. Is it because of the 1:n relationship?

Does someone have an idea how I could count the occupancy per day on the basis of the given checkin and checkout days? Was my solution approach right or is there any other way?

Would be really happy about answers!

Thanks and Best Regards.

[v-shex-msft · Accepted solution]

Hi @TestNutzer,

Basde on your description, you want to get the daily count of check in customers, right?

If as I said, you can refer to below steps:

check-in table :

 

Calendar table:

Measure:

CheckIn Count= 

var currDate=MAX(DateTable[Date])
var temp= ADDCOLUMNS(CheckIn,"range",CONCATENATEX(CALENDAR([date-in],[date-out]),[Date],","))
return
COUNTX(FILTER(temp,ISERROR(SEARCH(currDate,[range]))=FALSE()),[item])

Create a line chart and drag datetabel's date column to Axis field, "Check In Count" to Values field.

Regards,

Xiaoxin Sheng