Forum Discussion
Power Bi Attendance Calculation (mom wise)
Hi All,
Please help me calculate the attendance in power bi,
Joing Date= when a employee joins the company
LWD=when a employoess leaves
Active Stage=Active/Inactive,
Active=employee is still working
Inactive=employee has resigned
I need to map the attendance for both employees:
I was able to find the active working period but need help in mapping it month on month wise.
if employee is Active then consider LWD=Today else LWD as per record
Active Days= if [Active Stage] = "Active" then
Duration.Days(
Date.From(DateTime.LocalNow()) - [Joining]
)
else
Duration.Days([LWD] - ([Joining]))
)
Next Step: I manullly added the months
[
Dec22=0,
Jan23=0,
Feb23=0,
Mar23=0,
Apr23=0,
May23=0,
Jun23=0,
Jul23=0,
Aug23=0,
Sep23=0,
Oct23=0,
Nov23=0,
Dec23=0,
Jan24=0,
Feb24=0,
Mar24=0,
Apr24=0,
May24=0,
Jun24=0,
Jul24=0,
Aug24=0,
Sep24=0,
Oct24=0,
Nov24=0,
Dec24=0
]
Attendace for month Code Needed???? Please help!!
Excel formula that was used in excel:
=IFERROR(SUMPRODUCT(--((MONTH(ROW(INDIRECT($AV2 & ":" & IF($AT2="-",TODAY(),$AT2))))&YEAR(ROW(INDIRECT($AV2 & ":" & IF($AT2="-",TODAY(),$AT2)))))=MONTH(BX$1)&YEAR(BX$1))),"-")
Av1=joining date
At1=LWD
BY1=01-01-2023( atteandace for Jan'23)
6 Replies
Hello sunny18pc - it would be much better to do this kind of calculation with a DAX measure instead of Power Query. I have outlined the steps below and have also attached a sample pbix for you.
- Load your table into the data model
- add a date table (DAX calculated table)
- Create two relationships between your data table and the Date table (one on the Joining Date and another on the LWD date).
- Create a measure to return the number of employees that were active between the selected start and end date.
- Add a matrix visual to see the results.
Hi jennratten,
Thanks for you inputs,
Here is the sample for your reference:
I need the attendance for both active and inactive employee, However for active employees LWD= todays date
Emp id Joining Active Stage LWD Dec-22 Jan-23 Feb-23 Mar-23 Apr-23 May-23 Jun-23 Jul-23 Aug-23 Sep-23 Oct-23 Nov-23 102289903 25-Jul-23 Inactive 27-Sep-23 0 0 0 0 0 0 0 7 31 27 0 0 102290552 25-Jul-23 Inactive 26-Jun-24 0 0 0 0 0 0 0 7 31 30 31 30 102289725 25-Jul-23 Inactive 13-Mar-24 0 0 0 0 0 0 0 7
31 30 31 30 102290822 25-Jul-23 Inactive 03-Aug-23 0 0 0 0 0 0 0 7 3 0 0 0 102166787 05-Dec-22 Inactive 02-Jul-23 27 31 28 31 30 31 30 2 0 0 0 0 102166769 05-Dec-22 Inactive 05-Dec-23 27 31 28 31 30 31 30 31 31 30 31 30 102166177 05-Dec-22 Inactive 05-Dec-23 27 31 28 31 30 31 30 31 31 30 31 30 102166774 05-Dec-22 Inactive 01-Apr-23 27 31 28 31 1 0 0 0 0 0 0 0 102291568 26-Jul-23 Inactive 03-Feb-24 0 0 0 0 0 0 0 6 31 30 31 30 102257086 26-Jul-23 Inactive 01-Dec-23 0 0 0 0 0 0 0 6 31 30 31 30 102291686 26-Jul-23 Inactive 20-Nov-23 0 0 0 0 0 0 0 6 31 30 31 20 102291688 26-Jul-23 Inactive 06-Sep-23 0 0 0 0 0 0 0 6 31 6 0 0 102293911 26-Jul-23 Inactive 01-Feb-24 0 0 0 0 0 0 0 6 31 30 31 30 102294405 26-Jul-23 Inactive 05-Dec-23 0 0 0 0 0 0 0 6 31 30 31 30 102166171 05-Dec-22 Inactive 01-Feb-23 27 31 1 0 0 0 0 0 0 0 0 0 102291707 26-Jul-23 Inactive 12-Jun-24 0 0 0 0 0 0 0 6 31 30 31 30 102298741 14-Aug-23 Inactive 01-Dec-23 0 0 0 0 0 0 0 0 18 30 31 30 102166136 05-Dec-22 Inactive 05-Dec-23 27 31 28 31 30 31 30 31 31 30 31 30 102303403 14-Aug-23 Inactive 09-Dec-23 0 0 0 0 0 0 0 0 18 30 31 30 102166157 05-Dec-22 Inactive 05-Dec-23 27 31 28 31 30 31 30 31 31 30 31 30 102303164 14-Aug-23 Inactive 04-Jun-24 0 0 0 0 0 0 0 0 18 30 31 30 102303884 14-Aug-23 Inactive 20-Jun-24 0 0 0 0 0 0 0 0 18 30 31 30 102166772 05-Dec-22 Inactive 05-Dec-23 27 31 28 31 30 31 30 31 31 30 31 30 102303131 14-Aug-23 Inactive 26-May-24 0 0 0 0 0 0 0 0 18 30 31 30 102166191 05-Dec-22 Inactive 05-Dec-23 27 31 28 31 30 31 30 31 31 30 31 30 102166793 05-Dec-22 Inactive 05-Dec-23 27 31 28 31 30 31 30 31 31 30 31 30 102166786 05-Dec-22 Inactive 05-Dec-23 27 31 28 31 30 31 30 31 31 30 31 30 102166229 05-Dec-22 Inactive 05-Dec-23 27 31 28 31 30 31 30 31 31 30 31 30 102225372 26-Jul-23 Active - 0 0 0 0 0 0 0 6 31 30 31 30 102291622 26-Jul-23 Active - 0 0 0 0 0 0 0 6 31 30 31 30 102291806 26-Jul-23 Active - 0 0 0 0 0 0 0 6 31 30 31 30 You can easily accommodate this by replacing the LWD value for active employees with today's date.
The script for the Employees table in Power Query becomes this (go to the Advanced Editor and replace the contents with the script below):
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUfLMS0wuySxLBTLN9Y1M9Y0MjIyBbEt9I3MIO1YnWskJVaGhkT5YoRFEE0KdM1DAEa5K3xCsygTIVoqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, #"Active Stage" = _t, Joining = _t, LWD = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"Joining", type date}, {"LWD", type date}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,Date.From ( DateTime.FixedLocalNow() ),Replacer.ReplaceValue,{"LWD"}) in #"Replaced Value"Result
Hi sunny18pc, it is also possible in Power Query, but of course - DAX has better performance:
Result
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLVNTDXNTIwMlbSUTIy1zWwhHBiddDlzHQNzEAcE6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Joining = _t, LWD = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"Joining", type date}, {"LWD", type date}}), Helper = [ minDate = List.Min(ChangedType[Joining] & ChangedType[LWD]), maxDate = List.Max(ChangedType[Joining] & ChangedType[LWD]), dates = List.Dates(minDate, Duration.TotalDays(maxDate-minDate)+1, #duration(1,0,0,0)), months = List.Buffer(List.Distinct(List.Transform(dates, (x)=> Date.Year(x)*100 + Date.Month(x)))), monthsFormatted = List.Buffer(List.Transform(months, (y)=> Date.ToText(Date.From(Text.From(y) & "01"), [Format="MMM yy", Culture="en-US"]))) ], StepBack = ChangedType, Ad_MonthsList = Table.AddColumn(StepBack, "Months", each List.Distinct(List.Transform(List.Dates([Joining], Duration.TotalDays([LWD]-[Joining])+1, #duration(1,0,0,0)), (x)=> Date.Year(x)*100 + Date.Month(x))), type list), Ad_Months = List.Accumulate( List.Zip({Helper[months], Helper[monthsFormatted]}), Ad_MonthsList, (s,c)=> Table.AddColumn(s, c{1}, (x)=> if List.Contains(x[Months], c{0}) then 1 else 0, Int64.Type) ), RemovedColumns = Table.RemoveColumns(Ad_Months,{"Months"}) in RemovedColumns
