Calculating total number of sick days this month

[Anonymous]

I have a table of employee data for absence that looks like this:

EmpID       Name     AbsenceStartDate   AbsenceEndDate     Days Absent   

a124234   D Smith  12/10/2022              14/10/22                  3                              

I also have a separate Datetable and I have created inactive relationships between AbsenceStartDate, AbsenceEndDate and Datetable[Date].

I need to calculate the total days of absence for each employee in a given month so I can classify the absence as short-term or long-term (>=28 days is long term). What DAX code do I need?

[Anonymous · Accepted solution]

I solved this problem in the end by using this method to generate a list of dates between my startpoint and endpoint, and dividing the duration of sickness evenly between them. https://www.cloudfronts.com/blog/power-bi/how-to-list-all-dates-between-two-dates-in-powerbi-and-dis...

[Anonymous]

I have got a measure that sort of works - it calculates the total absence days up to the end of that month, but it's giving me a cumulative sum for the month instead of the absence days that fall *within* that month. Can anyone tell me how to tweak my code?

Currently using: 

Total Absence Days in Month =

VAR MinDate = MIN('Datetable'[Date])

VAR MaxDate=  MAX('Datetable'[Date])

VAR Result =

CALCULATE(

    SUMX('absence', DATEDIFF('absence'[Start Date], MaxDate, DAY)+1),

    'absence'[Start Date]<= MaxDate,

    'absence'[End Date] > MinDate,

    REMOVEFILTERS('Datetable'[Date])

)

RETURN

Result

[ImkeF]

Hi @Anonymous ,
looks like an events in progress pattern, the base measure could look like below. 
But you can find more twists here: Events in progress – DAX Patterns

VAR MinDate = MIN ( 'Date'[Date] )
VAR MaxDate = MAX ( 'Date'[Date] )
VAR Result =
    CALCULATE (
        COUNTROWS ( yourTable ),
        yourTable[AbsenceStartDate] <= MaxDate,
        yourTable[AbsenceEndDate] > MinDate,
        REMOVEFILTERS ( 'Date' )
    )
RETURN
    Result

[Anonymous]

Hi Imke, I get an error with your suggested formula: "DAX comparison operations do not support comparing values of type TEXT with values of type Date". I also changed COUNTROWS to SUM as I need to sum the days absent figure for each individual not count the rows.