Рет қаралды 44,429
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
• Events in Progress - d...
You can download my file and the source data here
aasolutions.sharepoint.com/:f...
Here are the formulae (NOTE that angled brackets aren't allowed in these KZbin 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
wyn.bio.link/