cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dstead0610 Frequent Visitor
Frequent Visitor

Count Number Of Events per date value MEASURE

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
v-huizhn-msft Super Contributor
Super Contributor

Re: Count Number Of Events per date value MEASURE

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.

 

1.PNG2.PNG

 

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.

5.PNG

Please let me know if you have any question.

Best Regards,
Angelia

5 REPLIES 5
Highlighted
v-huizhn-msft Super Contributor
Super Contributor

Re: Count Number Of Events per date value MEASURE

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.

 

1.PNG2.PNG

 

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.

5.PNG

Please let me know if you have any question.

Best Regards,
Angelia

Phil_Seamark Super Contributor
Super Contributor

Re: Count Number Of Events per date value MEASURE

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

dstead0610 Frequent Visitor
Frequent Visitor

Re: Count Number Of Events per date value MEASURE

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?

dstead0610 Frequent Visitor
Frequent Visitor

Re: Count Number Of Events per date value MEASURE

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

tpe Frequent Visitor
Frequent Visitor

Re: Count Number Of Events per date value MEASURE

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