cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Anonymous
Not applicable

## Number of sick days lost per month

Hi

I'm trying to calculate the number of sick days lost in any given month and just hitting a brick wall!

I have a table of absence data with an absence start date and absence end date (which can be blank due to absence not yet finished), and also a date table in my model.

I'm able to calculate the total days lost for each individual absence event, but what i would like to do is calculate the days lost in each month and then plot on a visual

The data looks a bit like the below;

The desired result would be

Feb days lost = 5

Mar days lost = 8

Any help would be greatly appreciated!

1 ACCEPTED SOLUTION
Super User

@Anonymous

you can try to create a date table and a column

``Column = COUNTROWS(FILTER('Table','Table'[Absence Start Date]<='date'[Date] &&('Table'[Absence End Date]>='date'[Date])||('Table'[Absence Start Date]<='date'[Date] &&ISBLANK('Table'[Absence End Date])&&'date'[Date]<=today())))``

pls see the attachment below

Proud to be a Super User!

5 REPLIES 5
Super User

@Anonymous

you can try to create a date table and a column

``Column = COUNTROWS(FILTER('Table','Table'[Absence Start Date]<='date'[Date] &&('Table'[Absence End Date]>='date'[Date])||('Table'[Absence Start Date]<='date'[Date] &&ISBLANK('Table'[Absence End Date])&&'date'[Date]<=today())))``

pls see the attachment below

Proud to be a Super User!

Anonymous
Not applicable

@ryan_mayu thank you! This appears to work perfectly and a tidy solution

Super User

you are welcome

Proud to be a Super User!

Anonymous
Not applicable

Thanks for the reply John, but i'm afraid this isn't giving me the desired result. For instance in my live dataset it's telling me just one day was lost on 28 Feb when i can see it's lots more from the raw data

Super User

Try

``````Days lost =
VAR MinDate =
MIN ( 'Date'[Date] )
VAR MaxDate =
MAX ( 'Date'[Date] )
VAR FullAbsenceTable =
GENERATE (
SUMMARIZE (
'Table',
'Table'[Employee ID],
'Table'[Absence start date],
'Table'[Absence end date]
),
VAR StartDate = 'Table'[Absence start date]
VAR EndDate =
MINX ( { 'Table'[Absence end date], TODAY (), MaxDate }, [Value] )
RETURN
CALENDAR ( StartDate, EndDate )
)
RETURN
COUNTROWS (
FILTER ( FullAbsenceTable, [Date] >= MinDate && [Date] <= MaxDate )
)
``````

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.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors