Replicate Data for Missing Dates

[mfrihart]

Date	Organization Hierarchies	Planning Hierarchies	Spread	Rate	AvgYield
9/30/2024	Region1	TOTAL LOANS	0.0391040	0.0503075	0.0894115
9/30/2024	Region1	TOTAL DEPOSITS	0.0195093	0.0523728	0.0328635
9/30/2024	Region2	TOTAL LOANS	0.0246889	0.0510887	0.0757776
9/30/2024	Region2	TOTAL DEPOSITS	0.0278904	0.0518805	0.0239902
12/31/2024	Region1	TOTAL LOANS	0.0363506	0.0455367	0.0818873
12/31/2024	Region1	TOTAL DEPOSITS	0.0276596	0.0574373	0.0297777
12/31/2024	Region2	TOTAL LOANS	0.0229423	0.0494140	0.0723563
12/31/2024	Region2	TOTAL DEPOSITS	0.0231505	0.0447552	0.0216047
3/31/2025	Region1	TOTAL LOANS	0.0062319	0.0445537	0.0507856
3/31/2025	Region1	TOTAL DEPOSITS	0.0269081	0.0546294	0.0277214
3/31/2025	Region2	TOTAL LOANS	-0.0088639	0.0475873	0.0387233
3/31/2025	Region2	TOTAL DEPOSITS	0.0327228	0.0512766	0.0185538

I have data published quarterly that looks like this. I want to create rows for the missing month-end dates that replicates the data from the most recent quarter end for every combination of Organization Hierarchies and Planning Hierarchies. So my output will look like this: 

Date	Organization Hierarchies	Planning Hierarchies	Spread	Rate	AvgYield
9/30/2024	Region1	TOTAL LOANS	0.0391040	0.0503075	0.0894115
9/30/2024	Region1	TOTAL DEPOSITS	0.0195093	0.0523728	0.0328635
9/30/2024	Region2	TOTAL LOANS	0.0246889	0.0510887	0.0757776
9/30/2024	Region2	TOTAL DEPOSITS	0.0278904	0.0518805	0.0239902
10/31/2024	Region1	TOTAL LOANS	0.0391040	0.0503075	0.0894115
10/31/2024	Region1	TOTAL DEPOSITS	0.0195093	0.0523728	0.0328635
10/31/2024	Region2	TOTAL LOANS	0.0246889	0.0510887	0.0757776
10/31/2024	Region2	TOTAL DEPOSITS	0.0278904	0.0518805	0.0239902
11/30/2024	Region1	TOTAL LOANS	0.0391040	0.0503075	0.0894115
11/30/2024	Region1	TOTAL DEPOSITS	0.0195093	0.0523728	0.0328635
11/30/2024	Region2	TOTAL LOANS	0.0246889	0.0510887	0.0757776
11/30/2024	Region2	TOTAL DEPOSITS	0.0278904	0.0518805	0.0239902
12/31/2024	Region1	TOTAL LOANS	0.0363506	0.0455367	0.0818873
12/31/2024	Region1	TOTAL DEPOSITS	0.0276596	0.0574373	0.0297777
12/31/2024	Region2	TOTAL LOANS	0.0229423	0.0494140	0.0723563
12/31/2024	Region2	TOTAL DEPOSITS	0.0231505	0.0447552	0.0216047
1/31/2025	Region1	TOTAL LOANS	0.0363506	0.0455367	0.0818873
1/31/2025	Region1	TOTAL DEPOSITS	0.0276596	0.0574373	0.0297777
1/31/2025	Region2	TOTAL LOANS	0.0229423	0.0494140	0.0723563
1/31/2025	Region2	TOTAL DEPOSITS	0.0231505	0.0447552	0.0216047
2/28/2025	Region1	TOTAL LOANS	0.0363506	0.0455367	0.0818873
2/28/2025	Region1	TOTAL DEPOSITS	0.0276596	0.0574373	0.0297777
2/28/2025	Region2	TOTAL LOANS	0.0229423	0.0494140	0.0723563
2/28/2025	Region2	TOTAL DEPOSITS	0.0231505	0.0447552	0.0216047
3/31/2025	Region1	TOTAL LOANS	0.0062319	0.0445537	0.0507856
3/31/2025	Region1	TOTAL DEPOSITS	0.0269081	0.0546294	0.0277214
3/31/2025	Region2	TOTAL LOANS	-0.0088639	0.0475873	0.0387233
3/31/2025	Region2	TOTAL DEPOSITS	0.0327228	0.0512766	0.0185538

It feels simple but I am struggling to execute. 

[Gunnar-Scheck]

Hi, 

I have attached a copy of my workflow that I created to solve this. You'll probably need to adapt it to your own needs. Below is a copy of my results and the workflow as a whole. 

In short, what I did was produce a list of dates from the earliest date in the data (9/30/2024) through the end date, which is the current month's date (4/30/2025). From there, I created 4 lines for each end of month and a record ID for each row and month. I combined the existing data to the new full end of month list and from there, used several multi-row formula tools to carry down the existing data to the months that had no data. 

Hope this helps!

[flying008]

Hi, @mfrihart 

FYI.

IMHO, the requirements for implementing this scenario can be divided into 3 steps:

1- Calculate and fill in all missing months based on the existing date.

2- Count the types based on the existing date and map them to the complete time interval above.

3- Append the original row to the corresponding month and count row.

The advantage of this is that it can be implemented dynamically, no matter how many types or rows there are in each month, even if the number or types of each month is different, no human intervention is required.

Date	Organization Hierarchies	Planning Hierarchies	Spread	Rate	AvgYield
2024-9-30	Region1	TOTAL LOANS	0.039104	0.0503075	0.0894115
2024-9-30	Region1	TOTAL DEPOSITS	0.0195093	0.0523728	0.0328635
2024-9-30	Region2	TOTAL LOANS	0.0246889	0.0510887	0.0757776
2024-9-30	Region2	TOTAL DEPOSITS	0.0278904	0.0518805	0.0239902
2024-10-31	Region1	TOTAL LOANS	0.039104	0.0503075	0.0894115
2024-10-31	Region1	TOTAL DEPOSITS	0.0195093	0.0523728	0.0328635
2024-10-31	Region2	TOTAL LOANS	0.0246889	0.0510887	0.0757776
2024-10-31	Region2	TOTAL DEPOSITS	0.0278904	0.0518805	0.0239902
2024-11-30	Region1	TOTAL LOANS	0.039104	0.0503075	0.0894115
2024-11-30	Region1	TOTAL DEPOSITS	0.0195093	0.0523728	0.0328635
2024-11-30	Region2	TOTAL LOANS	0.0246889	0.0510887	0.0757776
2024-11-30	Region2	TOTAL DEPOSITS	0.0278904	0.0518805	0.0239902
2024-12-31	Region1	TOTAL LOANS	0.0363506	0.0455367	0.0818873
2024-12-31	Region1	TOTAL DEPOSITS	0.0276596	0.0574373	0.0297777
2024-12-31	Region2	TOTAL LOANS	0.0229423	0.049414	0.0723563
2024-12-31	Region2	TOTAL DEPOSITS	0.0231505	0.0447552	0.0216047
2025-1-31	Region1	TOTAL LOANS	0.0363506	0.0455367	0.0818873
2025-1-31	Region1	TOTAL DEPOSITS	0.0276596	0.0574373	0.0297777
2025-1-31	Region2	TOTAL LOANS	0.0229423	0.049414	0.0723563
2025-1-31	Region2	TOTAL DEPOSITS	0.0231505	0.0447552	0.0216047
2025-2-28	Region1	TOTAL LOANS	0.0363506	0.0455367	0.0818873
2025-2-28	Region1	TOTAL DEPOSITS	0.0276596	0.0574373	0.0297777
2025-2-28	Region2	TOTAL LOANS	0.0229423	0.049414	0.0723563
2025-2-28	Region2	TOTAL DEPOSITS	0.0231505	0.0447552	0.0216047
2025-3-31	Region1	TOTAL LOANS	0.0062319	0.0445537	0.0507856
2025-3-31	Region1	TOTAL DEPOSITS	0.0269081	0.0546294	0.0277214
2025-3-31	Region2	TOTAL LOANS	-0.0088639	0.0475873	0.0387233
2025-3-31	Region2	TOTAL DEPOSITS	0.0327228	0.0512766	0.0185538

[Qiu]

@mfrihart 
I try with approach and find it is very interesting one. Maybe we can submit it as a weekly challenge idea 😁
I use a mapping file for the relation between Month and Quarter and it is easy to maintain as well.