How to get the amount of people currently present

[Anonymous]

Hi everyone,

I'm very new to DAX and now I'm having some trouble with a calculation I need to make. My question is how to get the amount of people that is present on a certain day (or month), based on the begin and end date and for which organisation group they are present. Due to confidential information I cant give you guys a data set, but I made an example which I hope will help my explanation. 

This is an example of the data I have. So I need to determine how many persons are present in a certain organisation group, while they can also be active in multiple groups at the same time. The end date shows when they left a certain group.

This next example made in Excel is what should be the outcome (I need to know the amount present after a certain day). This information should be presented based on a certain group in the organization.

[Anonymous]

Well, this is not clear enough. The Excel table does not show how Organization_ID affects the calculation.

Best

Darek

[Anonymous]

I should have called it Group_id. As you can see in the second excel it is the amount of people present at a certain group.

So what I eventually need is the amount of people that are present at a day, month, year for a certain group. Based on the amount of people that are coming in or are leaving (for that group).

Best,

Lars

[Anonymous · Accepted solution]

-- Say, you have a parameter table disconnected
-- from the model that stores all the days
-- you're interested in. Let's name it DaysOfInterest.
-- So, this table has only one column: Date.
-- Let's name the table you've posted Presence.

-- This measure gives the number of people
-- where StartDate >= [day of interest] and
-- ( EndDate <= [day of interest] or EndDate is BLANK ).
-- You should hide StartDate and EndDate and never
-- slice by them. For dates use the dates in
-- DaysOfInterest.

[# Present] =
var __oneDayOfInterestSelected = HASONEFILTER( DaysOfInterest[Date] )
var __dayOfInterest = VALUES( DaysOfInterest[Date] )
var __peoplePresent =
	CALCULATE(
		DISTINCTCOUNT( Presence[Person_ID] ),
		Presence[StartDate] >= __dayOfInterest,
		__dayOfInterest <= Presence[EndDate]
			|| ISBLANK( Presence[EndDate] )
	)
return
	if( __oneDayOfInterestSelected,  __peoplePresent )
	
-- The above measure will work OK whether
-- or not you're slicing by Group_ID. If
-- you don't, then it'll calculate the number
-- of people present on the selected day
-- regardless of which group they belong to.
-- In other words, if there is at least one
-- group a person belongs to on the selected day,
-- the person will be counted. If you filter
-- by group, then it'll show the the number
-- of people that belong to the group on the
-- selected day. By the way, you should change
-- the model to ease the calculation. Instead
-- of leaving blank in the EndDate, you should
-- put something like 9999-01-01 in there, so
-- that the || ISBLANK( Presence[EndDate] )
-- part can be removed. This will speed up
-- the calculation.

Best

Darek

[Anonymous]

Thanks for your answer, but the only problem now is that I get a (blank) value when I select a group for which I want to see the amount of people that are present.

Best,

Lars

[Anonymous]

Please paste the structure of the model in here.

Best

Darek

[Anonymous]

There was a problem with my model. It didn't have a relation with the organisation table. It works fine for me now. Thanks for your help!

Best,

Lars