Add Rows to Data Where No Events Happened in a Given Month

[fietzn]

Hi. I've spent some time searching and haven't been able to find anything to solve my challenge.

I have a fact table with events and dates for employees that I want to summarize by month year. If there were no events for a given month year though, I still want to show the employee ID and month year, but with a '0' (or null if '0' isn't possible). 

I created a master calendar of the months for a given period, but I'm not sure how to incorporate that, since it only keys off the month year field in the events table. Hoping someone can point me in the right direction. Thanks in advance.

Fact Data:

EMPLOYEE ID	DATE	AMOUNT	YEAR MONTH
123	11/1/2022	5	11/30/2022
123	11/16/2022	5	11/30/2022
123	12/16/2022	3	12/31/2022
123	3/5/2023	6	3/31/2023
123	5/4/2023	8	5/31/2023
123	8/19/2023	9	8/31/2023
123	10/1/2023	2	10/31/2023
456	10/13/2022	7	10/31/2022
456	11/15/2022	3	11/30/2022
456	3/16/2023	5	3/31/2023
456	3/19/2023	8	3/31/2023
456	5/1/2023	5	5/31/2023

Desired Result:

EMPLOYEE ID	YEAR MONTH	AMOUNT
123	10/31/2022	0
123	11/30/2022	10
123	12/31/2022	3
123	1/31/2023	0
123	2/28/2023	0
123	3/31/2023	6
123	4/30/2023	0
123	5/31/2023	8
123	6/30/2023	0
123	7/31/2023	0
123	8/31/2023	9
123	9/30/2023	0
456	10/31/2022	7
456	11/30/2022	3
456	12/31/2022	0
456	1/31/2023	0
456	2/28/2023	0
456	3/31/2023	13
456	4/30/2023	0
456	5/31/2023	5
456	6/30/2023	0
456	7/31/2023	0
456	8/31/2023	0
456	9/30/2023	0

[Jebrezov]

I too have a similar situation I’ve been thinking about. I haven’t tried it yet, but was thinking of adding a column for employeeid to the master calendar, then concatenate the employeeid and date together to use as the master key to join the master calendar and raw data. This should ensure there is a row for every employeeid and date combination which is where the issue lies.  Add in a coalesce statement on the amount column to input a 0 if the value is null and I think it will give your desired output.