need help getting information from second table

[SuchCT]

Hello everyone,

I have two data tables, table1 contains the name of the employee, the country where they work, the date worked and if they worked from home or the office.

table2 contains a list of holidays per country

Holiday	Location
1/1/2024	USA
1/1/2024	CAN
1/1/2024	UK
1/3/2024	CAN
1/4/2024	UK
5/3/2024	CAN
7/3/2024	UK
8/15/2024	CAN
11/27/2024	UK
12/8/2024	USA

I currently have a column calculating the amount of office days pfor that employee per week. But now need for a way to have another column that says how many holidays happened during that week in that country

Name	Date	Location	Worked  from	Office days this week
John	1/2/2024	USA	Office	1
Mary	1/2/2024	CAN	Office	2
Peter	1/2/2024	UK	Office	3
John	1/3/2024	USA	Home	1
Peter	1/3/2024	UK	Office	3
John	1/4/2024	USA	Office	1
Mary	1/4/2024	CAN	Home	2
John	1/5/2024	USA	Home	1
Mary	1/5/2024	CAN	Home	2
Peter	1/5/2024	UK	Home	3
John	1/6/2024	USA	Home	1
Mary	1/6/2024	CAN	Office	2
Peter	1/6/2024	UK	Office	3

[ThxAlot · Accepted solution]

holiday.pbix

Tricky data model makes all easy enough,

[sevenhills]

Calendar table: I created the date table in DAX. If you already have date table, ignore this step.

Date Table = 
 ADDCOLUMNS (
    CALENDAR("01/01/2024", "12/31/2024"),
    "Calendar Year", "CY " & YEAR ( [Date] ),
    "Month Name", FORMAT ( [Date], "mmmm" ),
    "Month Number", MONTH ( [Date] ),
    "Weekday", FORMAT ( [Date], "dddd" ),
    "Weekday number", WEEKDAY( [Date] ),
    "Week number", YEAR ( [Date] ) & "-W" & WEEKNUM( [Date] ),
    "Quarter", "Q" & TRUNC ( ( MONTH ( [Date] ) - 1 ) / 3 ) + 1
)

Data Model: Create relationships, if you already have it, ignore this step

Adding Week to your holiday: In the table 2, holidays table, adding this column (not measure) in DAX: Reason for adding the column is typically we may have multiple requests. Recommend to do this column in  Power Query or at the source.

Week Number = LOOKUPVALUE('Date Table'[Week number], 'Date Table'[Date], 'Table 2'[Holiday])

Similarly added two columns in your tx table, table 3, as below, in dax:

Optional column: Current week number

Current Week Number = LOOKUPVALUE('Date Table'[Week number], 'Date Table'[Date], 'Table 3'[Date])

Column for your need:

Holiday Count this Week = 
 -- Adding as column
 Var currentWeek = LOOKUPVALUE('Date Table'[Week number], 'Date Table'[Date], 'Table 3'[Date])
 Var HolidayWeeklyCnt = CALCULATE( COUNTROWS('Table 2'), FILTER( ALLSELECTED('Table 2'), 'Table 2'[Week Number] = currentWeek && 'Table 3'[Location] = 'Table 2'[Location]))
 Return HolidayWeeklyCnt

May be there are shorter ways as I did not see the whole data model, wrote this.
If you need as a measure, then tweak the DAX and tune it to your needs!

Finally in the table visual

Hope it helps!