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

tpe's avatar
tpe
New Member
8 years ago

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