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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
wellington491
New Member

Availability Table

Hello,

I'm creating a dashboard to display the future occupancy of a hotel's apartments, based on information from the reservations that have been made in the system. But I found myself in a problem that I still haven't found a solution. When I load the system data that are saved in the DB SQL SERVER, the table that has the reservation information returns the following data.

 

144

 

Each ID shown in the image above represents a reservation, then the arrival date and departure date and finally the number of adults and children that are in this reservation. To create this availability panel, I need to distribute the number of adults and children over the period they stayed, and on the day of departure these people cannot be counted. I'm using two reservations below for example.

 

145.PNG

 

On the 04/03th, 8 adults from the ID 5 reservation entered and 4 adults from the ID 6 reservation entered
On the 04/03th, 8 children from the ID 5 reservation entered and 3 children from the ID 6 reservation entered

Adults = 8+4=12
Children = 8+3=11

Result of the day 04/03 - 12 Adults and 11 Children

On 05/04 - There was no entry and exit, so I will only count who is already staying

On 06/04 the ID 6 reservation came out so I have to subtract the amount of children and adults
Adults = 12-4=8
Children = 11-3=8

Result of the day 06/03 - 8 Adults and 8 Children

On 06/04 the ID 5 reservation came out so I have to subtract the amount of children and adults

Adults = 8-8=0
Children = 8-8=0

Result of the day 07/03 - 0 Adults and 0 Children


Is it possible to do this data modeling within BP?

Thank you for your help

1 ACCEPTED SOLUTION
SteveHailey
Solution Specialist
Solution Specialist

Hello @wellington491. Something like the measures below should work.

 

I created a .pbix here in case it's helpful. 

 

 

Adults =
VAR CurrentDate =
    MAX ( Dates[Date] )
VAR Result =
    CALCULATE (
        SUM ( Data[ADULT] ),
        Data[INPUT] <= CurrentDate
            && Data[OUTPUT] > CurrentDate
    )
RETURN
    Result
Children =
VAR CurrentDate =
    MAX ( Dates[Date] )
VAR Result =
    CALCULATE (
        SUM ( Data[CHD] ),
        Data[INPUT] <= CurrentDate
            && Data[OUTPUT] > CurrentDate
    )
RETURN
    Result

 

 

SteveHailey_0-1643660678633.png

 

 

View solution in original post

3 REPLIES 3
wellington491
New Member

Hello @SteveHailey Thank you very much, it worked perfectly.

 

Follow the result below.

 

146.PNG

Taking advantage of the opportunity, can you kindly let me know how I can hide in the visual, the lines that already have past dates?

Once again, thank you very much for your attention.

You're welcome! I think the simplest way to hide rows that have past dates would be to select the table, and then in the filter pane add a Relative date filter; something like what is shown below:

 

SteveHailey_0-1643666613338.png


It could also be done with DAX if you don't love the above method.

SteveHailey
Solution Specialist
Solution Specialist

Hello @wellington491. Something like the measures below should work.

 

I created a .pbix here in case it's helpful. 

 

 

Adults =
VAR CurrentDate =
    MAX ( Dates[Date] )
VAR Result =
    CALCULATE (
        SUM ( Data[ADULT] ),
        Data[INPUT] <= CurrentDate
            && Data[OUTPUT] > CurrentDate
    )
RETURN
    Result
Children =
VAR CurrentDate =
    MAX ( Dates[Date] )
VAR Result =
    CALCULATE (
        SUM ( Data[CHD] ),
        Data[INPUT] <= CurrentDate
            && Data[OUTPUT] > CurrentDate
    )
RETURN
    Result

 

 

SteveHailey_0-1643660678633.png

 

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.