Max guest on which date

Hi Anonymous ,

 

For simplicity of calcuation, I'd create a custom column of Dates of Stay in Power Query using the Coming and Leaving Dates. I can then use this column in a relationship with my Dates table. I would just count the number of rows per date in the Dates of Stay column to count how many guest are in a specific date.

 

Connect to a blank query as a data source, delete everything and then paste the code below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIy1Dew0DcyMDICcYzhnFidaCUjDHlTFHljVC1AjgmKvAlIyAJJ3gCo3xIqHwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [GuestID = _t, #"Coming Date" = _t, #"Leaving Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"GuestID", type text}, {"Coming Date", type date}, {"Leaving Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Dates of Stay", each let 
daycount = Duration.Days([Leaving Date]-[Coming Date])+1
in List.Dates([Coming Date], daycount, #duration(1,0,0,0)), type list),
    #"Expanded Dates of Stay" = Table.ExpandListColumn(#"Added Custom", "Dates of Stay"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Dates of Stay",{{"Dates of Stay", type date}})
in
    #"Changed Type1"

 

Sample result

 

Anonymous 
To find out the number of guest by date,( This works even even without relationships):

No of Guests = 
VAR __CurrDate = SELECTEDVALUE('Dates'[Date])
RETURN
COUNTROWS(
    FILTER(
        ALL(Table3),
        __CurrDate >= Table3[Coming Date] && __CurrDate <= Table3[Leaving Date] 
    )
)

Add the dates from date table and the above measure:

If you need to calcualte the date when you had the maximum guest count, create the following measure and use it on a card visual.

Max Guest Count = 
VAR __DateCount = 
    ADDCOLUMNS(
        VALUES('Dates'[Date]),
        "Count",
        VAR  __CurrDate = 'Dates'[Date]
        RETURN
        COUNTROWS(
            FILTER(
                ALL(Table3),
                __CurrDate >= Table3[Coming Date] && __CurrDate <= Table3[Leaving Date] 
            )
        )
    )
VAR __MaxCount = 
    MAXX( __DateCount,[Count])
VAR __MaxCountDate = 
    MAXX( FILTER( __DateCount , [Count] = __MaxCount) ,'Dates'[Date] )
RETURN
 __MaxCountDate