Forum Discussion

ninakarsa's avatar
ninakarsa
Icon for Helper II rankHelper II
6 years ago
Solved

calculating daily occupany rate

Hi

 

I am trying to find away to calculate the number of occupied spaces for a given day by parking area. For example, based on the arrival and departure dates how many customers have an occupied parking spot on a given day. And then to be able to drill down to view the chart/table by parking area.

  • ninakarsa's avatar
    ninakarsa
    6 years ago

    az38 What i want to be able to see is by date the occupied spaces and to be able to drill into zone as below. And to have the total displayed. Thanking you in advance

     

     

    The table below shows the correct values for zone, but not for left, right. The measure used for this is: 

    Measure 5 = CALCULATE(COUNTROWS(bookings), FILTER(ALL(bookings), bookings[adate]<=SELECTEDVALUE(CalendarAreasTable[Date]) && bookings[ddate]>=SELECTEDVALUE(CalendarAreasTable[Date]) && bookings[car park area]=SELECTEDVALUE(CalendarAreasTable[car park area])) )

    If I use measure 8, I get the correct values for car park (left/right) but there is no value displayed for zone.

    Measure 8 = CALCULATE(COUNTROWS(bookings), FILTER(ALL(bookings), bookings[adate]<=SELECTEDVALUE(CalendarAreasTable[Date]) && bookings[ddate]>=SELECTEDVALUE(CalendarAreasTable[Date]) && bookings[car park area]=SELECTEDVALUE(CalendarAreasTable[car park area]) && bookings[car park]=SELECTEDVALUE(CalendarAreasTable[car park]) ) )

     

     

18 Replies

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi ninakarsa 

    Your question is very complex and, obviously, there is not enough source data to give you a full and complex answer. but i try

    first, the best practice is to create Calendar table for your task.

    you could do this by create a calculated table like this

     

    CalendarTable = CALENDARAUTO()

     

    or

     

    CalendarTable = CALENDAR (DATE (2019, 1, 1), DATE (2019, 12, 31))

     

    next, you can create a dimension table 'Parking Areas' which define all reported areas (one area = one row in the table).

     

    next, you can create a table CalendarAreasTable that will be looked like

    DateParking Area
    01.01.2019Zone A
    01.01.2019Zone B
    01.01.2019Zone C
    02.01.2019Zone A
    02.01.2019Zone B
    02.01.2019Zone C
    03.01.2019Zone A
    03.01.2019Zone B
    03.01.2019Zone C

    to do that create a new calculated table

     

    CalendarAreasTable = CROSSJOIN(CalendarTable;'Parking Areas')

     

    next you can add a simple measure to your new CalendarAreasTable:

     

    Measure = 
CALCULATE(COUNTROWS('DataTable'),
FILTER(ALL('DataTable'), 'DataTable'[Departure Date]<=SELECTEDVALUE('CalendarAreasTable'[Date]) && 'DataTable'[Departure Date]>=SELECTEDVALUE('CalendarAreasTable'[Date]) && 'DataTable'[Parking Area]=SELECTEDVALUE('CalendarAreasTable'[Parking Area]))
)

     

    where 'DataTable' is a table with log of your parking records

    So, this measure will give you quantity of customers for each day by each Area and you could drill down it easily

     

    do not hesitate to give a kudo to useful posts and mark solutions as solution

    LinkedIn

    • ninakarsa's avatar
      ninakarsa
      Icon for Helper II rankHelper II
      6 years ago

      Thanks for your quick response, so I will attached below the tables I have and what i want to do:

       

      So Table A has the booking information, table b has the info that i want to drill by and table c is the calender

       

       

      Table A: Bookings

      Customer IDadateddatecar parkcar park area
      12301/05/201905/05/2019LeftZone A
      12402/05/201904/05/2019rightZone B
      12503/05/201905/05/2019LeftZone C
      12604/05/201906/05/2019rightZone A
      12701/05/201903/05/2019LeftZone B
      12802/05/201904/05/2019LeftZone C
      12903/05/201905/05/2019LeftZone A
      13001/05/201903/05/2019LeftZone B
      13102/05/201904/05/2019LeftZone C
      13203/05/201905/05/2019LeftZone A
      13301/05/201903/05/2019LeftZone B
      13402/05/201904/05/2019LeftZone A
      13503/05/201905/05/2019rightZone B
      13605/05/201907/05/2019rightZone C
      13706/05/201908/05/2019rightZone A
      13804/05/201906/05/2019rightZone A
      13905/05/201907/05/2019leftZone B
      14006/05/201908/05/2019leftZone C
      14108/05/201910/05/2019leftZone A
      14209/05/201911/05/2019leftZone B
      14307/05/201909/05/2019leftZone C
      14408/05/201910/05/2019leftZone A
      14509/05/201911/05/2019leftZone B
      14611/05/201913/05/2019leftZone C
      14712/05/201914/05/2019leftZone A
      14810/05/201912/05/2019leftZone B
      14911/05/201913/05/2019leftZone A
      15012/05/201914/05/2019leftZone B

       

      Table B: Info

      car parkcar park area
      LeftZone A
      rightZone B
       Zone C

       

      Table C: Calendar

       

      Table 😧 

      crossjoin(calendar,Info)

       

      Then I use the following calcuation to give me the number of occupied spaces on a given day: but this is not returning any results

      Measure =
      CALCULATE(COUNTROWS(bookings),
      FILTER(ALL(bookings), bookings[adate]>=SELECTEDVALUE(CalendarAreasTable[Date]) && bookings[ddate]<=SELECTEDVALUE(CalendarAreasTable[Date]) && bookings[car park area]=SELECTEDVALUE(CalendarAreasTable[car park area]))
      )

       

       

       

      • az38's avatar
        az38
        Icon for Community Champion rankCommunity Champion
        6 years ago

        hi ninakarsa 

        it seems you're confused marks < and >

        try

        Measure =
CALCULATE(COUNTROWS(bookings),
FILTER(ALL(bookings), bookings[adate]<=SELECTEDVALUE(CalendarAreasTable[Date]) && bookings[ddate]>=SELECTEDVALUE(CalendarAreasTable[Date]) && bookings[car park area]=SELECTEDVALUE(CalendarAreasTable[car park area]))
)

        do not hesitate to give a kudo to useful posts and mark solutions as solution

        LinkedIn