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
youtu.be/ISDhR-TzwJk
You can download my file and the source data here
aasolutions.sharepoint.com/:f:/g/AA_CPort/EhoCc6G0b8JGoYYV-uMspvQBoZQQljisdJ6Ba2qGZnZXFw?e=Id0j80
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
wyn.bio.link/
- DAX and the Start Date End Date Problem aka Events In Progress ( Download)
- DAX measure with start AND end date for Power BI ( Download)
- Events in Progress - displaying items present between two dates in Power BI / Excel with Power Query ( Download)
- Working With Time Tables - Problem Of The Week #10 (DAX Solution) ( Download)
- Abstract Thesis 95: Power BI- DAX: Get all dates between the Start and End date ( Download)
- DAX Fridays #208: Open orders in DAX | Events in progress & Time intelligence ( Download)
- Generate Dates between Start and End Date in Power Query ( Download)
- Generate dates between Start Date and End Date in Power BI ( Download)
- Power BI Pattern Find Start and End Date of Task from Children ( Download)
- Power BI - CALENDARAUTO 1899 and how to fix it #DAX #DOSEOFBI ( Download)
- When is a date not a date in Power BI ( Download)
- Calculate OPEN CASES over time in Power BI | Part 1 ( Download)
- Power BI - Events in Progress Part 1 ( Download)
- Quickly Discover When The Last Date Something Occurred Was Using DAX - MAXX Tutorial ( Download)
- Interview Question | Last Purchase Date | Advanced DAX | Power BI ( Download)
