Classify number of working days between 2 dates

[frankhofmans]

hi PBI community members:

i have a data table with a employee ID, the startdate and the end date of a sickness case. i want to present the number of working days in a graph, and i want to classify the number of working days in 3 categories. The way of the classification is depending on wether or not the sickness end date is in or later than the selected period:

For example:

Employee ID	Start date	End date
23	03-04-2020	05-04-2020
27	04-04-2020	03-07-2020
30	03-03-2020	10-08-2020
35	01-01-2020	05-01-2020
40	05-02-2020	10-02-2020

I want the following table or graph:

Class:	Q1 2020	Q1 2020	Q3 2020
0-5 working days	7	1	0
6-30 working days	21	0	0
30-999 working days	0	127	32

I need the number of working days between start date and end date, but if the end date is later than the end date of the selected period (in this case quaterly), than i need the number of working days between start date and last date of the quarter. So the sickness case of employee 30 (start date 03-03-2020) is classified in Q1 as "5-30 working days", but in Q2 it is classified as "30-999 working days". 

Does anyone have a solution for this?

Thanks in advance,

Regards,

Frank

[v-robertq-msft · Accepted solution]

Hi, @frankhofmans 

According to your description and sample data, I get the expected result through calculated column and matrix, you can take a look at my method and find if it’s useful:

1. I created these calculated columns:

Q1 2020 =
var _endofquater=DATE(2020,3,31)
var _datediff=DATEDIFF('Table'[Start date],_endofquater,DAY)
var _compare=DATEDIFF('Table'[End date],_endofquater,DAY)
var _value= IF(_compare<0,ABS(_datediff),BLANK())
return
SWITCH(TRUE(),
_value>0&&_value<=5,"0-5 working days",
_value>6&&_value<=30,"6-30 working days",
_value>30&&_value<=999,"30-999 working days",
"Ineligible")

Q2 2020 =
var _endofquater=DATE(2020,6,30)
var _datediff=DATEDIFF('Table'[Start date],_endofquater,DAY)
var _compare=DATEDIFF('Table'[End date],_endofquater,DAY)
var _value= IF(_compare<0,ABS(_datediff),BLANK())
return
SWITCH(TRUE(),
_value>0&&_value<=5,"0-5 working days",
_value>6&&_value<=30,"6-30 working days",
_value>30&&_value<=999,"30-999 working days",
"Ineligible")

Q3 2020 =
var _endofquater=DATE(2020,9,30)
var _datediff=DATEDIFF('Table'[Start date],_endofquater,DAY)
var _compare=DATEDIFF('Table'[End date],_endofquater,DAY)
var _value= IF(_compare<0,ABS(_datediff),BLANK())
return
SWITCH(TRUE(),
_value>0&&_value<=5,"0-5 working days",
_value>6&&_value<=30,"6-30 working days",
_value>30&&_value<=999,"30-999 working days",
"Ineligible")

2. Then I created a Matrix chart and place columns like this:

And I guess this is what you want.

You can download my test pbix file here

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@frankhofmans , 2 Steps, First you need create a measure for working day diff and then you need to do dynamic segmentation

Refer this file : https://www.dropbox.com/s/o2v5ekfiw5057do/Working%20date%20between%20Dates%20Measure%20and%20Table.p...

or

https://www.sqlbi.com/articles/counting-working-days-in-dax/

Dynamic segmentation: https://youtu.be/CuczXPj0N-k