Split days within date range into respective calendar weeks

[bpitbp]

Hi everyone,

I am struggling to find a solution to the following problem:

I have a table with employee names and a leave from and leave until date. I have created the sum between the two dates (ex. weekends) so I have the total days of the absence of each employee.

The problem is that I want to know their absence days of each calendar week (if they are absent) and that is an issue for absences that span more than 1 calendar week (= 2 different calendar week numbers).

- E.g.: employee Nicole has 14 absence days from 11 April until 24 April 2022, so she is absent in week no. 15-17.

I have:
- Slicer with week no.
- table visual with names and absences > shows that e.g. Nicole is absent 15 days in week no. 15 (which is wrong)

-> correct would be:

- week no. 15: 5 abscence days

- week no. 16: 5 abscence days
- week no. 17: 3 abscence days

employee	leave - from	leave - until	days
Nicole	11.4.2022 (week no. 15)	24.4.2022 (week no. 17)	13

Ideally I would like a measure so I can subtract the absence days in the calendar weeks from the target weekly work hours. 

Any tip is appreciated and thank you very much in advance!

[Anonymous · Accepted solution]

Hi  @bpitbp ,

Here are the steps you can follow：

1. Create a calendar table.

Date =
CALENDAR(
DATE(2022,1,1),
DATE(2022,12,31))

Create calculated column.

Weeknum =
WEEKNUM('Date'[Date],2)

Weekday =
WEEKDAY('Date'[Date],2)

IF =
IF(
'Date'[Weekday] in {6,7},0,1)

2. Create measure.

IF_Measure =
IF(
MAX('Date'[Date])<MAX('Table'[leave-from]),
BLANK(),
SUMX(FILTER(ALLSELECTED('Date'),
'Date'[Date]>=MAX('Table'[leave-from])&&
'Date'[Date]<=MAX('Date'[Date])),[IF])
)

Flag =
IF(
MAX('Date'[Date])>=
MINX(FILTER(ALLSELECTED('Date'),[IF_Measure]<>BLANK()),
[Date])&&
MAX('Date'[Date])<=
MAXX(FILTER(ALL('Date'),[IF_Measure]=MAX('Table'[days])),[Date])
&&
MAX('Date'[Date])=MINX(FILTER(ALL('Date'),'Date'[Weeknum]=MAX('Date'[Weeknum])),[Date])
,1,0)

Count =
COUNTX(FILTER(ALL('Date'),
'Date'[Weeknum]=MAX('Date'[Weeknum])&&'Date'[IF]=1&&
'Date'[Date]>=
MINX(FILTER(ALLSELECTED('Date'),[IF_Measure]<>BLANK()),
[Date])&&
'Date'[Date]<=
MAXX(FILTER(ALL('Date'),[IF_Measure]=MAX('Table'[days])),[Date])
),[IF])

concatenation1 =
COMBINEVALUES(": ","week no."&" "&MAX('Date'[Weeknum]),[Count]&" "&"abscence days")

concatenation2 =
CONCATENATEX(ALLSELECTED('Date'),
[concatenation1]," "&"
"&
"")

3. Place [Flag]in Filters, set is=1, apply filter.

4. Result:

Best Regards,

Liu Yang

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