Count no. of missing weekdays for previous week

Question

I have a table that contains employee names and dates worked.

Sample Data:

Name    Date         
Tim     2020/08/16
Tim     2020/08/17   
Tim     2020/08/19   
Sarah   2020/08/18
Sarah   2020/08/19
Sarah   2020/08/20
Sarah   2020/08/21

I also have a date dimension table (if it is required for this).

I would like to count (displayed on a card) the number of missing weekdays from the previous week. So in this example, Tim would be 3 (18th, 20th and 21st Aug) and Sarah would be 1 (17th Aug).

Ideally this will reset every Saturday e.g. if today is Sat 22nd Aug, it will show the results for period 15-21 Aug. It will then update the following Saturday.

Thanks in advance for any help.

Angelo Canepa Angelo Canepa · Accepted Answer · 2020-08-23T22:50:52

Assuming your Calendar table look like this:

+---------------------+---------+------------+
|         Date        | WeekDay | WeekNumber |
+---------------------+---------+------------+
| 16/08/2020 00:00:00 | False   | 33         |
+---------------------+---------+------------+
| 17/08/2020 00:00:00 | True    | 34         |
+---------------------+---------+------------+
| 18/08/2020 00:00:00 | True    | 34         |
+---------------------+---------+------------+
| 19/08/2020 00:00:00 | True    | 34         |
+---------------------+---------+------------+
| 20/08/2020 00:00:00 | True    | 34         |
+---------------------+---------+------------+
| 21/08/2020 00:00:00 | True    | 34         |
+---------------------+---------+------------+
| 22/08/2020 00:00:00 | False   | 34         |
+---------------------+---------+------------+
| 23/08/2020 00:00:00 | False   | 34         |
+---------------------+---------+------------+
| 24/08/2020 00:00:00 | True    | 35         |
+---------------------+---------+------------+
| 25/08/2020 00:00:00 | True    | 35         |
+---------------------+---------+------------+
| 26/08/2020 00:00:00 | True    | 35         |
+---------------------+---------+------------+
| 27/08/2020 00:00:00 | True    | 35         |
+---------------------+---------+------------+

The table was created used the min Date from the Sample Data (table you shared). The code of the table is below, basically shows two columns, one with the date and another with a boolean showing true for weekday and false for the weekend.

Also, assumes a relationship between calendar and the sampledata table, by the column date.

Calendar =
ADDCOLUMNS (
    CALENDAR ( MIN ( SampleData[Date] ), TODAY () ),
    "WeekDay", WEEKDAY ( [Date], 2 ) < 6,
    "WeekNumber", WEEKNUM ( [Date], 2 )
)

The measure calculates the difference between the two tables, only considering weekdays for the calendar table.

Missing Weekday =
VAR lastweek =
    WEEKNUM(TODAY()-WEEKDAY(TODAY(),2),2)
VAR AllDates =
    SUMMARIZE (
        FILTER (
            'Calendar',
            'Calendar'[WeekDay] = TRUE
                && 'Calendar'[WeekNumber] = lastweek
        ),
        'Calendar'[Date]
    )
VAR EmployeesDates =
    VALUES ( SampleData[Date] )
VAR ndays =
    COUNTROWS ( EXCEPT ( AllDates, EmployeesDates ) )
RETURN
    IF ( ISBLANK ( ndays ), 0, ndays )

The output

Edit:

I have changed the SampleData to consider more "edge" cases to test the measure.

+-------+------------+
|  Name |    Date    |
+-------+------------+
| Tim   | 16/08/2020 |
+-------+------------+
| Tim   | 17/08/2020 |
+-------+------------+
| Tim   | 19/08/2020 |
+-------+------------+
| Tim   | 25/08/2020 |
+-------+------------+
| Sarah | 18/08/2020 |
+-------+------------+
| Sarah | 19/08/2020 |
+-------+------------+
| Sarah | 20/08/2020 |
+-------+------------+
| Sarah | 21/08/2020 |
+-------+------------+
| Eric  | 17/08/2020 |
+-------+------------+
| Eric  | 18/08/2020 |
+-------+------------+
| Eric  | 19/08/2020 |
+-------+------------+
| Eric  | 20/08/2020 |
+-------+------------+
| Eric  | 21/08/2020 |
+-------+------------+

Count no. of missing weekdays for previous week

1 Answers