Forum Discussion
Split days within date range into respective calendar weeks
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!
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
1 Reply
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