Forum Discussion

sunny18pc's avatar
sunny18pc
Regular Visitor
2 years ago

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 s...
Need Help
Show and Tell
jennratten's avatar
jennratten
Super User
2 years ago

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.

 

sunny18pc's avatar
sunny18pc
Regular Visitor
2 years ago

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 idJoiningActive StageLWDDec-22Jan-23Feb-23Mar-23Apr-23May-23Jun-23Jul-23Aug-23Sep-23Oct-23Nov-23
10228990325-Jul-23Inactive27-Sep-2300000007312700
10229055225-Jul-23Inactive26-Jun-240000000731303130
10228972525-Jul-23Inactive13-Mar-240000000
 

7

31303130
10229082225-Jul-23Inactive03-Aug-23000000073000
10216678705-Dec-22Inactive02-Jul-232731283130313020000
10216676905-Dec-22Inactive05-Dec-23273128313031303131303130
10216617705-Dec-22Inactive05-Dec-23273128313031303131303130
10216677405-Dec-22Inactive01-Apr-232731283110000000
10229156826-Jul-23Inactive03-Feb-240000000631303130
10225708626-Jul-23Inactive01-Dec-230000000631303130
10229168626-Jul-23Inactive20-Nov-230000000631303120
10229168826-Jul-23Inactive06-Sep-230000000631600
10229391126-Jul-23Inactive01-Feb-240000000631303130
10229440526-Jul-23Inactive05-Dec-230000000631303130
10216617105-Dec-22Inactive01-Feb-2327311000000000
10229170726-Jul-23Inactive12-Jun-240000000631303130
10229874114-Aug-23Inactive01-Dec-230000000018303130
10216613605-Dec-22Inactive05-Dec-23273128313031303131303130
10230340314-Aug-23Inactive09-Dec-230000000018303130
10216615705-Dec-22Inactive05-Dec-23273128313031303131303130
10230316414-Aug-23Inactive04-Jun-240000000018303130
10230388414-Aug-23Inactive20-Jun-240000000018303130
10216677205-Dec-22Inactive05-Dec-23273128313031303131303130
10230313114-Aug-23Inactive26-May-240000000018303130
10216619105-Dec-22Inactive05-Dec-23273128313031303131303130
10216679305-Dec-22Inactive05-Dec-23273128313031303131303130
10216678605-Dec-22Inactive05-Dec-23273128313031303131303130
10216622905-Dec-22Inactive05-Dec-23273128313031303131303130
10222537226-Jul-23Active-0000000631303130
10229162226-Jul-23Active-0000000631303130
10229180626-Jul-23Active-0000000631303130
  • jennratten's avatar
    jennratten
    Super User
    2 years ago

    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