Forum Discussion
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")
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
5 Replies
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,
AngeliaI need to change my model a little bit due to that i have relationships between both tables, but works great!
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
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') )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?
