Merge 2 tables with date fields which are measured daily and monthly

[Fletcher345]

Hello everyone,

I have a problem trying the create a relationship between to tables with date fields:

Table 1: Workforce Data:

Fields: Date (monthly), Workforce numbers (number of employees at that date)

Table 2: Covid-19 Data

Fields: Individual's name, Date contracted Covid-19 (Daily)

I need to create a pivot table which compares accumulated Covid-19 cases per day with the total workforce number for that day (given the workforce numbers are measured monthly, we would use the same workforce number at the beginning of the month for the entire month).

Any advise would be appreciated, thanks in advance!

[#2 · Accepted solution]

@Fletcher345 Attached a workbook with a very crude example of what could be achieved in Power Pivot without much difficulty.

[#3]

Thank you @Riny_van_Eekelen that was exaction what I wanted to achieve.