DAX and the Start Date End Date Problem aka Events In Progress
45.5 هزار بار بازدید -
2 سال پیش
-
Calculating the number of people
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/
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/
2 سال پیش
در تاریخ 1400/12/07 منتشر شده
است.
45,593
بـار بازدید شده