sql code in Dax

Hey ninakarsa ,

first I created a very simple calendar table using this DAX (there are much more sophisticated DAX statements available, just search for DAX calendar):

Calendar = 
var DateStart = MIN('Table'[Date Arrival])
var DateEnd = MAX('Table'[Date Departure])
return
CALENDAR(DateStart , DateEnd) 

Please make sure that there is no relationship between the Calendar table and the containing the arrival and departure date, this table is called table in my example.

I created the measure "present" using this DAX:

present = 
var _date = CALCULATE(MAX('Calendar'[Date]))
return
CALCULATE(
    COUNTROWS('Table')
    , FILTER(
        'Table'
        , 'Table'[Date Arrival] <= _date && 'Table'[Date Departure] >= _date
    )
)

and the measure arrived using this DAX:

arrived = 
var _date = CALCULATE(MAX('Calendar'[Date]))
return
CALCULATE(
    COUNTROWS('Table')
    , FILTER(
        'Table'
        , 'Table'[Date Arrival] = _date
    )
)

This allows to create a table visual like so:

Please be aware that the date column in the table visual above is the date column from the Calendar table.

If you are searching for event-in-progress you will find some articles that provide more background for similar questions than my answer will be able to provide.

Hopefully this is what you are looking for

Regards,

Tom