cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

Determining missing row information for current time period compared to last time period

We are having a problem with occasionally missing a person’s pay period due to a location switch. For example, Jane Doe works on one project the previous week and switches to another this week. The new foreman forgets to add them to the timecard at the new location. Therefore, no record exists at all.

What I am trying to do is identify those that were on payroll last week and flag them if they are missing this week. If they are missing, there is no row entry in the table of them existing at all.

Any suggestion on how to compare the two time periods?

1 ACCEPTED SOLUTION
Community Support

Hi @efstel ,

I recommend that you calculate the total number of days of attendance and then subtract the number of days each person was in regular attendance to get the number of days each person was absent.

Suppose you want to check the number of days of absence for everyone from 9/6 to 9/1, you can refer to this.

All attendance records from 9/6 to 9/1.

All employees.

Calculate the number of days each employee is absent.

``Absence = DATEDIFF(DATE(2024,9,1),DATE(2024,9,6),DAY)-CALCULATE(COUNTROWS(Arrived),Arrived[Name]=MAX(Staff[Name]))``

Best regards,

Mengmeng Li

5 REPLIES 5
Community Support

Hi @efstel ,

I recommend that you calculate the total number of days of attendance and then subtract the number of days each person was in regular attendance to get the number of days each person was absent.

Suppose you want to check the number of days of absence for everyone from 9/6 to 9/1, you can refer to this.

All attendance records from 9/6 to 9/1.

All employees.

Calculate the number of days each employee is absent.

``Absence = DATEDIFF(DATE(2024,9,1),DATE(2024,9,6),DAY)-CALCULATE(COUNTROWS(Arrived),Arrived[Name]=MAX(Staff[Name]))``

Best regards,

Mengmeng Li

Helper I

That's not quite what I am looking for. I need to pull the names of the actual persons not in attendance. I don't need a count.

Responsive Resident

Hi,

As you havenot shared any sample data, I assumed the dataset as below

Based on this employee C and D missing this week.

here how I flaghged it using measure

Is this whate you are asking?

Responsive Resident

Create a dummy table of all the employees considering present all the weeks. Now with this table compare your actual one

Helper I

Okay I have a table that shows all active employees. I want to list the dates where they aren't showing up at all. Since there is no record on that date, that employee doesn't show up in the system that day at all. I'd also like to graphically show a count of missing time entrys on each date as well.

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors