Hello everyone
I’ve followed this topic ( Count periods of employee absence over consecutive days - DAX Calculations - Enterprise DNA Forum) to count continuous student absences.
I need some help to modify the DAX to continue counting when a weekend breaks the sequence, but not during the week.
I’ve attached my file and data. Any advice would be very helpful.
Regards, Dale
Absences.zip (119.3 KB)
@dalef - Microsoft just released a new DAX function NETWORKDAYS in July 2022 Release of Power BI Desktop. Can you check if this helps your scenario?
Hello @dalef,
Thank You for posting your query onto the Forum.
In order to achieve the results based on the condition which you’ve specified i.e., countinuance in count of absentee days without breaking the sequence when there’s a weekend in between.
So first you’ll be required to do the transformations inside the Power Query and once it’s done there then writing DAX becomes much easier in order to achieve the final results. Below is the M Code as well as DAX formulas alongwith the screenshot of the final results provided for the reference -
let
Source = Excel.Workbook(File.Contents("Absences\AbsenceEvents.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"StudentID", Int64.Type}, {"AbsenceTypeCode", type text}, {"AbsenceDate", type date}}),
#"Inserted Day Name" = Table.AddColumn(#"Changed Type", "Day Name", each Date.DayOfWeekName([AbsenceDate]), type text),
#"Grouped Rows" = Table.Group(#"Inserted Day Name", {"StudentID"}, {{"FullTable", each _, type table [StudentID=nullable number, AbsenceTypeCode=nullable text, AbsenceDate=nullable date, Day Name=text]}}),
Custom1 = Table.TransformColumns(#"Grouped Rows",{{"FullTable", each Table.AddIndexColumn(_, "Index")}}),
#"Added Custom" =
Table.AddColumn(Custom1, "Custom", each
let
AllDataTable = [FullTable],
PreviousRowValue =
Table.AddColumn(
Table.AddColumn(
Table.AddColumn( AllDataTable, "Previous Day Name", each try AllDataTable [Day Name] {[Index] - 1} otherwise null ),
"Previous Absence Date", each try AllDataTable [AbsenceDate] {[Index] - 1} otherwise null ),
"Final Previous Absence Date", each if [Previous Absence Date] = null then [AbsenceDate] else [Previous Absence Date])
in
PreviousRowValue),
Custom2 = Table.Combine(#"Added Custom"[Custom], {"StudentID", "AbsenceTypeCode", "AbsenceDate", "Day Name", "Previous Day Name", "Final Previous Absence Date"}),
#"Added Custom1" =
Table.AddColumn(Custom2, "Dates", each
if ( [Day Name] = "Monday" and [Previous Day Name] = "Friday" ) and ( Number.From([Final Previous Absence Date])- Number.From([AbsenceDate]) ) = -3 then
let
AllDates = {Number.From([Final Previous Absence Date])+1..Number.From([AbsenceDate])},
DatesList = List.Distinct(List.Transform(AllDates, each Date.From(_)))
in
DatesList
else
{[AbsenceDate]}),
#"Expanded Dates" = Table.ExpandListColumn(#"Added Custom1", "Dates"),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded Dates",{"StudentID", "AbsenceTypeCode", "Dates"}),
#"Inserted Day Name1" = Table.AddColumn(#"Removed Other Columns", "Day Name", each Date.DayOfWeekName([Dates]), type text),
#"Changed Type1" = Table.TransformColumnTypes(#"Inserted Day Name1",{{"Dates", type date}}),
#"Grouped Rows1" = Table.Group(#"Changed Type1", {"StudentID"}, {{"FullTable", each _, type table [StudentID=nullable number, AbsenceTypeCode=nullable text, Dates=nullable date, Day Name=text]}}),
Custom3 = Table.AddColumn(#"Grouped Rows1", "Custom", each
let x =[FullTable], Dates=x[Dates],
Custom1 = List.Generate(()=>[i=0,k=Dates{i},j=1],
each [i]<List.Count(Dates),
each [i=[i]+1,k=Dates{i},j=if Duration.Days(k-[k])=1 then [j] else [j]+1], each [j]),
Custom2 = Table.ColumnNames([FullTable]),
Custom3 = Table.ToColumns([FullTable]),
Custom4 = List.Combine({Custom2,{"Grouping"}}),
Custom5 = Table.FromColumns(Custom3&{Custom1},Custom4)
in
Custom5),
#"Removed Other Columns1" = Table.SelectColumns(Custom3,{"Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns1", "Custom", {"StudentID", "AbsenceTypeCode", "Dates", "Day Name", "Grouping"}, {"StudentID", "AbsenceTypeCode", "Dates", "Day Name", "Grouping"}),
#"Grouped Rows2" = Table.Group(#"Expanded Custom", {"StudentID", "Grouping"}, {{"FullTable", each _, type table [StudentID=number, AbsenceTypeCode=text, Dates=date, Day Name=text, Grouping=number]}}),
Custom4 = Table.AddColumn(#"Grouped Rows2", "Custom.1", each
let
x = [FullTable],
y = Table.Sort(x,{{"Dates", Order.Ascending}}),
z = Table.AddIndexColumn(y, "Sequence", 1, 1, Int64.Type)
in z),
#"Removed Other Columns2" = Table.SelectColumns(Custom4,{"Custom.1"}),
#"Expanded Custom.1" = Table.ExpandTableColumn(#"Removed Other Columns2", "Custom.1", {"StudentID", "AbsenceTypeCode", "Dates", "Day Name", "Grouping", "Sequence"}, {"StudentID", "AbsenceTypeCode", "Dates", "Day Name", "Grouping", "Sequence"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Expanded Custom.1",{{"StudentID", Int64.Type}, {"AbsenceTypeCode", type text}, {"Dates", type date}, {"Day Name", type text}, {"Grouping", Int64.Type}, {"Sequence", Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type2", each ([Day Name] <> "Saturday" and [Day Name] <> "Sunday")),
#"Removed Other Columns3" = Table.SelectColumns(#"Filtered Rows",{"StudentID", "AbsenceTypeCode", "Dates", "Grouping", "Sequence"})
in
#"Removed Other Columns3"
Once the above transformation is done, you’ll be required to create a relationship with the Dates table -
Now, create two calculated columns to achieve the required results -
1). Calculating Minimum Absence Date
Min Absence Date =
CALCULATE( MIN( Data[Dates] ) ,
ALLEXCEPT( Data , Data[StudentID] , Data[Grouping] ) )
2). Calculating the Days Difference Ignoring the Weekends -
Days Difference =
CALCULATE( COUNTROWS( Dates ) ,
DATESBETWEEN(
Dates[Date] ,
Data[Min Absence Date] ,
Data[Dates] ) ,
Dates[IsWorkingDay] = TRUE() ,
ALL( Data ) )
Finally, you’ll be able to see the expected results -
I’m also attaching the working of the PBIX as well as the Excel file of cross-verification of results alongwith the links of the courses on M language or Power Query for the reference purposes.
Hoping you find this useful and meets your requirements that you’ve been looking for.
Thanks and Warm Regards,
Harsh
Count of Absent Days Excluding Weekends - Harsh.pbix (3.1 MB)
Cross-Verification of Results - Harsh.xlsx (200.4 KB)
Thanks for the tip pranamg. I can’t see a way to use that function with my data as I only have a start date but I’m sure it will be useful in other circumstances.
This is exactly what I need Harsh. Thank you very much. I’m gong to have to do a crash course in M code to understand what you’ve done so thanks for the links to the training resources 
Kind regards, Dale
