How to calculate presence

[Sof]

Hi everyone,

I have three columns, one with arrival dates, one with leaving dates and one with ID's. I want to create a graph which shows how many ID's are present at each moment. Because I also need the data of which ID is present when, the count needs to be linked to the ID's. Is this possible? And if so, how can I achieve this?

Thanks!

[bdymit]

Hello,

1. Can you please post the headers of the columns of each of the tables?

Example

Table 1                              Table 2                                    Table 3

ID      Arrival Date             ID          Leaving Date             ID           Customer Name

345    3/14/2017               345        3/17/2017                 345         Joe Smith

768    3/15/2017               768        3/16/2017                 768         Jane Doe

2. Can you describe any relationships that you currently have between the tables?

3. Let me know if this chart is what you want to see.

Thanks,

Ben

[Sof]

Hi,

By table I meant column, I'm new to power BI so I'm not familiar yet with all the terminology. Sorry, for the confusion. So currently I have:

Table 1

ID      Arrival Date             Leaving Date           

345    3/14/2017 13:15     3/17/2017  17:32               

768    3/15/2017  08:57    3/16/2017  15:18       

The chart I want to see is similar to that chart, but also by the hour. Each day thus consists of 24 elements on the x-axis.

I hope this answers your questions.  

[bdymit]

It is possible to count the IDs, and then have those expand out so you can see which exact IDs you are counting (with a matrix in Power BI)

Like this

[Sof]

How do you count the ID's based on date and time? I have a starting date and ending date which I know how to use. But how can I count an ID for a date between the starting and ending date? (Like you did for Jane Doe on March 15th in your example)

[bdymit · Accepted solution]

So the first thing you need is another table called "Calendar" that has a complete list of all the dates and times for the time period you are looking at. So you would have 24 rows for each date you are looking at. Then, you would have the table that you mentioned above with the ID, Start Date, and End Date. These tables would be unrelated.

Here is the basic concept:

You will have the Dates/Times (from your Calendar Table) on the axis of your graph or on the rows of your matrix, depending on how you want to see the data.

You then need a few measures to obtain a beginning and ending date/time value. Then, you will have another measure that checks your IDs table and sees if the given date/time is between the values listed. If the date is between the start/ end dates of a record, it keeps the record. Otherwise, it throws it out. Then it counts how many records are remaining.

If you have tables like this:

Presence Table

Calendar Table

Then write the following measures:

MinDate =
MIN ( 'Calendar'[DateTime] )

MaxDate =
MAX ( 'Calendar'[DateTime] )

NumIDs =
CALCULATE (
    COUNTROWS ( 'Presence Table' ),
    ALL ( 'Presence Table' ),
    FILTER (
        'Presence Table',
        AND (
            'Presence Table'[Arrival Date] <= [MinDate],
            'Presence Table'[Leaving Date] >= [MaxDate]
        )
    )
)

Num IDs Present =
IF (
    HASONEVALUE ( 'Calendar'[DateTime] ),
    [NumIDs],
    MAXX ( 'Calendar', [NumIDs] )
)

The measure "Num IDs Present" is the only one you should use in your visuals. It is able to handle the cases in which you are looking at a level less granular than Date/Time. It gives you the max number of ID's for the period of time you are looking at.

You can hide the other ones from Report View.

Hopefully this helps, I know this is a complicated answer to your question, especially since you are just starting out.

Here are some helpful resources:

sqlbi.com

powerpivotpro.com

daxformatter.com

Let me know if I can clarify anything!

[Sof]

It worked, thank you very much!