Calculating the number of people present or processes running at the end of the data or during the month is tricky and requires some clever DAX thinking

I've also now done a Power Query version of this
[ Ссылка ]


You can download my file and the source data here
[ Ссылка ]



Here are the formulae (NOTE that angled brackets aren't allowed in these YouTube comments so I've used the words *LESSOREQUAL* and *GREATER* instead. You will need to replace those .


Number of Guests Present at end of Period =
VAR EndDatePerVisual = MAX('Calendar'[Date])
VAR RESULT =
CALCULATE(
[Number of Guests Checked In],
REMOVEFILTERS('Calendar'),
BookingsData[Check in Date] *LESSOREQUAL* EndDatePerVisual,
BookingsData[Checkout Date] *GREATER* EndDatePerVisual
||
ISBLANK( BookingsData[Checkout Date] )
)
RETURN
RESULT


Number of Guests Present during the Period =
VAR EndDatePerVisual = MAX('Calendar'[Date])
VAR StartDatePerVisual = Min('Calendar'[Date])
VAR RESULT =
CALCULATE(
[Number of Guests Checked In],
REMOVEFILTERS('Calendar'),
BookingsData[Check in Date] *LESSOREQUAL* EndDatePerVisual,
BookingsData[Checkout Date] *GREATER* StartDatePerVisual
||
ISBLANK( BookingsData[Checkout Date] )
)
RETURN
RESULT



Connect with me on LinkedIn and Twitter via
[ Ссылка ]

свернуть