topic Re: Determining missing row information for current time period compared to last time period in DAX Commands and Tips

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

efstel — Thu, 05 Sep 2024 12:12:16 GMT

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?

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

Rupak_bi — Thu, 05 Sep 2024 13:35:56 GMT

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

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

efstel — Thu, 05 Sep 2024 14:57:06 GMT

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.

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

Anonymous — Fri, 06 Sep 2024 07:48:35 GMT

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

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

efstel — Fri, 13 Sep 2024 12:18:38 GMT

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.

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

Rupak_bi — Sat, 14 Sep 2024 10:16:20 GMT

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?