Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
koshur
Helper I
Helper I

Ideas for employee Attendance/timesheet Dashboard

Hi, 

 

I have been asked to design a dash for empl attandnce. I haven't really done any timesheet dashboards before. I did someting in excel which was basic pivot chart. Attaching the raw data and my current excel graph. 

 

What would be the best way to visualize and prepare the data. HR will send these excel dumps monthly. There is no on prem gateway for now, so excel upload and refresh is what we are aiming to achive. Not good with DAX so formulas would be a plus if you guys can share. 

 

  1. Column A - G is auto generated
  2. Column H - I is what i added for myself to create graph
  3. Leave hours is shared from ERP so has to be added manually monthly (not sure how to handle it - maybe an IF condition or a filter in BI to select # of leave days that would add/delete the # of productive hours from the visuals?)
  4. Shortfall and Utilization is what i believe can be automated as calculated columns.

I would appreciate any help with Idea and suggestions. 

 

http://www.filedropper.com/an_2 - raw file

Screen Shot 2016-11-16 at 7.35.46 AM.png

 

1 ACCEPTED SOLUTION

Hi @koshur,

Sorry for the wrong PBIX file. Please download the correct PBIX file from the following link, if you have any problems using the new PBIX file, please let me know.

https://1drv.ms/u/s!AhsotbnGu1NogjTT9NqW-QVosaAj

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

9 REPLIES 9
v-yuezhe-msft
Microsoft Employee
Microsoft Employee

Hi @koshur,

I make a test using your Excel file. It is easy to create above visuals in Power BI Desktop, after connecting to Excel file, you would need to add another calendar table in the current Power BI Desktop file and create relationship using date fields between the imported tables. Then you can create a Matrix visual and a clustered column chart using MonthName fields of calendar table and a set of fields of Excel Sheet. You can check the following screenshot , for more details, please review this attached PBIX file.
1.PNG

In addition, to refresh the report you created in Power BI Desktop without on-premises gateway, you can consider to store Excel in OneDrive for business, then connect to it from Power BI Desktop, create above report and upload Power BI Desktop file to Power BI Service. In this case, no gateway is required, Power BI automatically connects to the Excel workbook on OneDrive, about every hour to check for updates. If the Excel workbook has changed, Power BI will refresh the dataset and reports in the Power BI service.

   
Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Lydia,

 

Thanks for the clue. Can you send in the pbix that contains the data measures created as mentioned in your snapshot. I am also thinking to add a cal column for daily utilization % and monthly utilization % per emp. I'll try to do that and let you know how it works. My aim is to not do anyting inside excel but all within BI and calculated columns or measures.

 

Best

Hi @koshur,

I have attached PBIX file in my first reply, based on your current screenshot, there is no need to create measures or calculated columns, just add another calendar table. You can try to create measures or calculated columns to calculate daily utilization % and monthly utilization % per emp in the PBIX file I provided.

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Lydia,

 

For some reasons the PBIX you attached looks different on my end than the one in your snapshot.

 

EMP.jpg 

 

 

Hi @koshur,

Sorry for the wrong PBIX file. Please download the correct PBIX file from the following link, if you have any problems using the new PBIX file, please let me know.

https://1drv.ms/u/s!AhsotbnGu1NogjTT9NqW-QVosaAj

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

thanks, I'll try to play around, for sheet 1, i'm planning to have a separate cvs or excel for each emp in team and then have a team visual showing overall numbers and utilization. I'll try few things and let you know how it goes.

Hi , 

 

What i gave you was a file for one employee, how do i handle multiple employess so that i can have a filter for employee name that slices the data accordingly. 

Should I Import multiple xlxs for each emp wiht EMP name as unique among all (invloves adding a column in current table as EMP name)?

 

What is the best approach?

 

Hi @koshur,

Since this thread is closed, could you please create another new thread in the Power BI Desktop forum and describe your new requirement?

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BhaveshPatel
Community Champion
Community Champion

I don't think you need expert DAX formulas to visualize what you have. I have not spend much time looking at your data but at first sight, it looks like drag and drop on the canvas and you would be able to visualize what you are expecting.

 

 

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.