Count unbroken sick days include weekends

DAX DAX Calculations
1

Hi All, greetings from Denmark.

I have an issue regarding counting sick days including weekend’s /holidays

I need to find all employees that have the latest countinius sickdays over 30/45/70 days including weekends / holidays, counting from (today) and back.

It has to be an unbroken chain “calculation”

Employee 111295

eks. Sick from.
01-feb-2021 to 22-mar-2021 =50 days inc weekends

If the person returns within the 50 days for 1 day, the calulation have to reset, and start counting from 1 again.

I have “day of week” and “week” and “weekends” in my date table

image
image254×725 5.23 KB

image
image1039×100 6.19 KB

2

DNA-Count unbroken sick days include weekends.pbix (16.7 KB)

3

thx for reply, still unsure how to read you example, cant it be done with messures ?

4

Hi @Jens_H! We noticed that your inquiry was left unsolved for quite some time now. We strive to answer ALL inquiries in the forum. However, if you are sure that you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, you can check out tutorials to learn this yourself as your membership also comes with relevant resources that may help you with your Power BI education.

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!

5

What is not clear in the answer I gave you?
Probably the solution can be obtained using DAX functions, but I don’t know the DAX language.

6

Maybe adding a calculated column could work for you. You can use the values as a filter

FILTER Days =
VAR _Days = INT( Tabella[end] ) - INT( Tabella[start] ) + 1 

RETURN
SWITCH(
    TRUE(),
    _Days > 70,
    "+70", _Days > 45,
    "+45", _Days > 30,
    "+30", "Less"
)
7

The issue is that I dont a start and end date
I have alot of single absence days pr. employee

8

@Jens_H, the solution for your question is provided by @sprmnt21! For this kind of problems it is a best practice to use Power Query. It simplifies your model and your DAX inside the report.