Forum Discussion
Number of Days between two dates split by month - Employee Data
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
11 Replies
- barritown
Solution Sage
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
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
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] ) )
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
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.
see screenshot of my visual
The number per sickness is correct, but the total at the bottom is incorrect
