Forum Discussion
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.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
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?