Number of Days between two dates split by month - Employee Data

[DummyBIDev]

Hi All

Hoping you guys can direct me in the right direction, i've spent hours searching the forums and tried various suggesstions but nothing seems to work as i need it to. 

This post will be a bit detailed which hoping is a good thing

I have a set of tables

Date Table - This has a list of dates with the month, year, quarter, and a working day indicator

Sickness Table -This has a list of employee sicknesses - Employee ID, Sickness Start Date, Sickness End Date, Sick Reason

What i'd like to do

So the ask here is, i would like to create a measure that will show the number of sick days split by month. The data is currently like this 

Employee ID	Sick Start Date	Sick End Date
1	02/01/2023	10/01/2023
2	20/01/2023	04/02/2023
3	20/01/2023	10/02/2023
4	31/01/2023	08/03/2023

For the above data i would like it to be presented like this

Month	Emp	Sick Start Date	Sick End Date	Days
January	1	02/01/2023	10/01/2023	7 Days (This is to ex. holidays as per indicator in DATE table)
January	4	31/01/2023	08/03/2023	1 Day (ONLY 1 day absent in the month of January)
February	4	31/01/2023	08/03/2023	19 Days
February	4	31/01/2023	08/03/2023	5 Days

So essentially i would like the number of days off to be split by month.

I have been trying to do this and i believe i was close. I have tried to relate the tables with DATE AND SICKNESS START DATE but when i relate them i am unable to get the split like above - if i do not relate the tables then i can get the split.

Another issue i was having is, i want to be able to sum up the total sick days to use it in a key metric visual

Hope that is enough detail, would appreicate any help

Thanks

[DummyBIDev]

Thanks @barritown 

That measure works in a table, however the total of the measure doesn't add up correctly. How can i get the total to add up correctly? I.e. the total should show 48 but is showing as 21

[barritown]

@DummyBIDev,

Could you please clarify the problem for me with a screenshot or another PBIX file?

On my screenshot the total value is 48 so I don't get your point.

[DummyBIDev]

see screenshot of my visual

The number per sickness is correct, but the total at the bottom is incorrect

[barritown]

Don't you have some filter applied there or something else in the model I should be aware of? 

Any chance you can reproduce this problem with some mocking data and share PBIX?

P. S. It seems like your Total is showing the maximum value.

[DummyBIDev]

No filters applied on the table.

Just to be safe i created a whole new workbook and tried the filters but still the same issue

I've created a pbix file with dummy data which i'm hoping helps

Thanks

https://www.transfernow.net/dl/20231107VnVrATAN

[barritown]

Hi @DummyBIDev,

I guess you need something like the measure below in the model where your fact table is not related to your calendar table.

You can check how it works in the attached file.

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[DummyBIDev]

I've downloaded the file but i can see your file has the same issue. The total figure of 48 is incorrect if you sum up all the individual values of the "Counter" column

[barritown]

@DummyBIDev,

Agree, my arithmetic skills are in decline. 😅

I can propose another measure on top of the earlier one:

Result = 
IF ( ISFILTERED ( 'Date'[Month] ),
     [Counter],
     VAR _tbl = ADDCOLUMNS ( CROSSJOIN ( VALUES ( 'Date'[Month] ), 'Data Table' ), "Temp", [Counter] )
     RETURN SUMX ( _tbl, [Temp] ) )

[DummyBIDev]

Hi Thanks for sending this. In isolation this works perfectly, however as soon as i add a slicer to the report and filter for one specific month it goes back to the counting the number of days in the calendar rather than sickness days.

[barritown]

Hi, yup, the solution was not universal. 

You filter months and what else?

[DummyBIDev]

So i've got a date hierarchy on the 'Month Ending' date in my date table. I use the month from this hierarchy to filter results