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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Gav10
Helper II
Helper II

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;

 

Untitled.png

 

The desired result would be

 

Feb days lost = 5

Mar days lost = 8 

 

Any help would be greatly appreciated!

1 ACCEPTED SOLUTION
ryan_mayu
Super User
Super User

@Gav10 

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())))

 

 

1.PNG

pls see the attachment below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

5 REPLIES 5
ryan_mayu
Super User
Super User

@Gav10 

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())))

 

 

1.PNG

pls see the attachment below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

you are welcome





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Gav10
Helper II
Helper II

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

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors