cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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
Anonymous
Not applicable

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
Anonymous
Not applicable

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
Not applicable

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 @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
``````

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!

Anonymous
Not applicable

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.