Number of people absent on a day

[JVL]

Hello,

I have a table containing absences of employees including a start and an end date.

I created a date table having an active relationship between the date from the data table and the start date of the absence. There is also an inactive relationship between the date of the date table and the end date of the absence.

I would like to know the number of people absent on a specific day but I don't manage to find the correct DAX syntax. 

For the number of people being absent on the start date, the syntax I am using is: 

CALCULATE(COUNT(Absences[ID]))

For the people being absent on the end date of the absence, the syntax is: 

CALCULATE (COUNT ( Absences[ID] ), USERELATIONSHIP ( dateTable[DateAsInteger], Absences[DateAsIntegerEnd] )

As you can see, I created integers based on the dates and used these integers to build the relationships.

Can anyone provide a solution on how to calculate the number of people being absent on a given day (meaning where the day is equal to or bigger than the start date and equal to or smaller than the end date)?

Thank you,

Jos

[v-yohua-msft]

Hi, @JVL 

This is my train of thought, I create a table that assumes that all the dates are absent dates.

And then creates a new date table based on the smallest date and the largest date.

Table 2 = CALENDAR(MIN('Table'[DateAsInteger]),MAX('Table'[DateAsIntegerEnd]))

This is my model relationship.

Then create a measure to give a logo to the absentees.

Logo =
CALCULATE (
    COUNTAX (
        FILTER (
            'Table',
            MIN ( 'Table'[DateAsInteger] ) <= MIN ( 'Table 2'[Date] )
                && MIN ( 'Table 2'[Date] ) <= MAX ( 'Table'[DateAsIntegerEnd] )
                || MAX ( 'Table 2'[Date] ) >= MAX ( 'Table'[DateAsInteger] )
                    && MAX ( 'Table 2'[Date] ) <= MAX ( 'Table'[DateAsIntegerEnd] )
        ),
        ( 'Table'[ID] )
    ),
    CROSSFILTER ( 'Table'[DateAsInteger], 'Table 2'[Date], NONE )
)

Create a measure to count the total number of absences over a period of time, and use a slicer to see the number of absences over a period of time

sum = COUNTROWS('Table') 

Here is my preview

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data)

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Syndicate_Admin]

Buenos Dias to all.

In the case above, as would be the formula I have to add, I do have a person who has a sick leave start date but the leave end date is blank, that is, they are still on leave.

Thank you

Jose Luis

[JVL]

Thank you for the feedback @v-yohua-msft but this is not giving me the expected result.

I added some sample data below:

Absence_ID   Begindate   Enddate

1                   01/01/2024  01/01/2024

2                   02/01/2024  04/01/2024

3                   05/01/2024  15/01/2024

4                   05/01/2024  05/01/2024

5                   05/01/2024  07/01/2024

6                   10/01/2024  15/01/2024

7                   12/01/2024  31/01/2024

8                   16/01/2024  16/01/2024

9                   18/01/2024  31/01/2024

10                 18/01/2024  24/01/2024

11                 22/01/2024  24/01/2024

12                 24/01/2024  24/01/2024

13                 29/01/2024  04/02/2024

14                 30/01/2024  04/02/2024

15                 31/01/2024  05/02/2024

I would like to know how many people are absent on a specific day.

On 23/01 for example, this should show me 4 as ID's 7, 9, 10 and 11 are absent on 23/01/2024.

Any advise?

[v-yohua-msft · Accepted solution]

Hi, @JVL 

Create a new Table

Date = CALENDAR(MIN('Table'[Begindate]),MAX('Table'[Enddate])) 

Create two measures, the following is DAX

Measure = IF(MAX([Begindate])<= MAX('Date'[Date]) && MAX([Enddate]) >= MAX('Date'[Date]),1)

Sum Example = COUNTROWS(FILTER(ALLSELECTED('Table'),[Begindate]<= MAX('Date'[Date]) && [Enddate] >= MAX('Date'[Date])))

Here is my preview

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.