DAX and the Start Date End Date Problem aka Events In Progress

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 - displaying items...  


You can download my file and the source data here
https://aasolutions.sharepoint.com/:f...



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
https://wyn.bio.link/