Forum Discussion

dstead0610's avatar
dstead0610
Regular Visitor
9 years ago
Solved

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 occup...
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    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

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

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

dstead0610's avatar
dstead0610
Regular Visitor
9 years ago

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