Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
aganes
Frequent Visitor

Utilization Tracker - Show 0 hrs on days where no bookings were made

Hi Power BI Community,

 

I am looking to create a utilization tracker for rooms that I rent that shows all unutilized time even on days where there were no bookings.

 

I have three tables:

  • BookingsTable - for all booking info at the transaction level (‘BookingsTable’[Utilized] in this case being # of hrs that the room is rented on a given order)
  • RoomsTable - for all room info (‘RoomsTable ‘[Room] in this case for the room identifier)
  • CalendarTable - a DAX-derived calendar table (‘CalendarTable’[Day] is all dates on the calendar even if there were no bookings)

Additional Info: BookingsTable links to RoomsTable via room ID and BookingsTable links to CalendarTable via booking start date. BookingsTable and RoomsTable are connected with DirectQuery from a SQL database.

_______________________

Each room is open for 12 hrs (unless there’s an overtime request) so I used this measure to calculate the standard unutilized time per day:

 

Unutilized =

IF((12*COUNTA(RoomsTable[Room])*COUNTA(CalendarTable[Day]))-BookingsTable[Utilized]<0,

0, (12*COUNTA(RoomsTable[Room])*COUNTA(CalendarTable[Day]))-BookingsTable[Utilized])

 

This gives me the desired outcome IF the room has been booked every single day ([Utilized]>0) as shown below with Room 10 (including every day in February 2019):

Room 10 Feb 2019.PNG

But, this does not give me what I’m looking for with rooms that are not booked every day, as shown below with Room 20 (which was only booked for 7 days in February 2019):

Room 20 Feb 2019.PNG

I would like for Room 20 to have an entry for every day even if [Utilized] = 0. I think it would need to be in a measure or through a derived table since we are summing orders by day, but I have yet to crack it.

 

Any help would be greatly appreciated!

Thank you,

Alex

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@aganes - 

Two potential solutions:

1. A Measure can be converted from BLANK to 0 by adding 0, like this:

Unutilized =
IF (
    (
        12 * COUNTA ( RoomsTable[Room] )
            * COUNTA ( CalendarTable[Day] )
    ) - BookingsTable[Utilized] < 0,
    0,
    (
        12 * COUNTA ( RoomsTable[Room] )
            * COUNTA ( CalendarTable[Day] )
    ) - BookingsTable[Utilized]
)
+ 0

2. In your table visual, you can select "Show items with no data".

Show Items With No Data.PNG

Hope this helps,

Nathan

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

@aganes - 

Two potential solutions:

1. A Measure can be converted from BLANK to 0 by adding 0, like this:

Unutilized =
IF (
    (
        12 * COUNTA ( RoomsTable[Room] )
            * COUNTA ( CalendarTable[Day] )
    ) - BookingsTable[Utilized] < 0,
    0,
    (
        12 * COUNTA ( RoomsTable[Room] )
            * COUNTA ( CalendarTable[Day] )
    ) - BookingsTable[Utilized]
)
+ 0

2. In your table visual, you can select "Show items with no data".

Show Items With No Data.PNG

Hope this helps,

Nathan

Thanks Nathan,

 

Your +0 measure worked in adding the missing days. I gave it a simple odification so days without rentals showed 12 hrs for Unutilized:

 

Unutilized 2 = 

IF( BookingsTable[Utilized] = 0, 12,

IF ( ( 12 * COUNTA ( RoomsTable[Room] ) * COUNTA ( CalendarTable[Day] ) ) - BookingsTable[Utilized] < 0, 0, ( 12 * COUNTA ( RoomsTable[Room] ) * COUNTA ( CalendarTable[Day] ) ) - BookingsTable[Utilized] )) + 0

 Unutilized 2.PNG

 

Thank you for the help,

Alex

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.