Count Number Of Events per date value MEASURE

[dstead0610]

hello, I am trying to count the number of "Occupied rooms" per calendar date.

so if there was say 6 reserves with diffrent "DateIN" and "DateOUT" I would be able to calculate how many rooms occupied there was every day. I have done this by using a column formula - but I need it to be a measure in order to be filtered by slicers.

Here is a simple example of what I would need:

BOOKINGID                DateIN                 DateOUT

000001                       01-01-2017          06-01-2017

000002                       01-01-2017          03-01-2017

000003                       02-01-2017          03-01-2017

000004                       03-01-2017          05-01-2017

000005                       08-01-2017          10-01-2017

DATE                     OCCUPIEDROOMS

01-01-2017                          2

02-01-2017                          3

03-01-2017                          3

04-01-2017                          2

05-01-2017                          2

06-01-2017                          1

07-01-2017                          0

08-01-2017                          1

09-01-2017                          1

10-01-2017                          1

here is a the formula i used to do this as a column:

OcupacionHL = CALCULATE(sum(F_Reservas[Personas]); FILTER(F_Reservas; (F_Reservas[Fecha Inicio] <= D_Calendario[FECHA]) && (F_Reservas[Fecha Fin] >= D_Calendario[FECHA])); F_Reservas[Hotel] = "Hotel Liberty")

[Phil_Seamark]

If you want a DAX table then you can try this approach too.  The advantage is you can build measures of this and perform other calculations like averages etc more easily.  Just click New Table on the modelling table and paste the following.

New Table = SUMMARIZE( 
                FILTER(
                    CROSSJOIN(Table1,CALENDARAUTO()),
                    [Date]>=[DateIn] 
                    && [Date]<=[DateOut]
                    ),
                    [Date],
                    "Occupied Rooms",COUNTROWS('Table1')
                    )

[dstead0610]

This works great as a table on its own! but I can relate it to my original Calendar Table because it says that it has circular dependencies.

How would I relate it to my other tables so the time dependency works?

[v-huizhn-msft · Accepted solution]

Hi @dstead0610,

For your expected result, it should be 4 for 03-01-2017, which includes 000001,000002,000003,000004 based on my understanding.

I try to reproduce your scenario using the following table sample table and get expected result.

Please create a measure to get each date in date table. You can use Max, min , or average, because they are same for unique row in table.

get date = MAX('Date'[DATE])

Then create a measure to calculated number of "Occupied rooms" per calendar date.

Occupied rooms = CALCULATE(COUNTA(FactTable[BOOKINGID ]),FILTER(FactTable,FactTable[DateIN]<='Date'[get date]&&FactTable[DateOUT]>='Date'[get date]))

Create a table visual, select the Date[Date] and measure as value, please see the following screenshot.

Please let me know if you have any question.

Best Regards,
Angelia

[tpe]

Hello,

Thanks for this answer but I can see a little problem with this solution. In the example of dstead06, there is tiny difference: there is a day with "0". I have the same problem, but your solution doesn't work for me as I need all "0" values, as all others.

Is there any way to produce a table with theses values please ?

I tried with a pivor table but empty values remains empty, I didn't fond a way to put in a"0" value. It's important for my next calculations.

Thanks for any help.

Best regards,

TPE

[dstead0610]

I need to change my model a little bit due to that i have relationships between both tables, but works great!