cancel
Showing results for
Did you mean:
New Member

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

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?

Thanks and Best Regards.

1 ACCEPTED SOLUTION
Community Support

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])
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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Community Support

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])
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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors