cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculating total number of sick days this month

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?

1 ACCEPTED SOLUTION
Frequent Visitor

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...

4 REPLIES 4
Frequent Visitor

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...

Frequent Visitor

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
Super User

Hi @kendrasaurus50 ,
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
``````

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

Frequent Visitor

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.

Announcements

#### Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

#### Power BI March 2023 Update

Find out more about the March 2023 update.

#### March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors