Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Shared Reservations via Open Tickets

Hello,

 

I'm utilizing the open tickets solution to solve hotel occupancy, from https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/td-p/409364, but there are some reservations that are shared.  Each reservation has it's own reservation number or ticket number, but they share a hotel room and share number.  Therefore using countrows overstates the number of room nights by double counting shares. I'm attempting to get a distinct count of the share numbers day by day that can be summarized. 

 

Using distinctcount('Table'[Share]) gives the correct number of shares per day, but of course doesn't work for mulitple days.  Using  

VAR a = SUMMARIZE('Occupancy Table','Occupancy Table'[Share],"t",DISTINCTCOUNTNOBLANK('Occupancy Table'[Share]))
RETURN SUMX(a,[t]) does the same thing.
 
CALCULATE(countx('Occupancy Table',DISTINCTCOUNT('Occupancy Table'[Share])),'Occupancy Table'[Share]<>blank()) gives the total number shared reservations, but gives the grand total on each day, and sums them. 
 
I've attempted several variations of 
Tickets Open = 
VAR tmpTickets = ADDCOLUMNS('Tickets',"Effective Date",IF(ISBLANK([Closed Date]),TODAY(),[Closed Date]))
VAR tmpTable =  
SELECTCOLUMNS(
    FILTER(
        GENERATE(
            tmpTickets,
            'Calendar'
        ),
        [Date] >= [Opened Date] &&
        [Date] <= [Effective Date]
    ),
    "ID",[Ticket Num],
    "Date",[Date]
)
VAR tmpTable1 = GROUPBY(tmpTable,[ID],"Count",COUNTX(CURRENTGROUP(),[Date]))
RETURN COUNTROWS(tmpTable1)

But all of my results have been no where accurate.

 

Here is a link to my test data. 

 

Thank you in advance for any assistance or direction.

Seth

 

 

No Replies