Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

View solution in original post

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.