Replicate Data for Missing Dates

Question

DateOrganization HierarchiesPlanning HierarchiesSpreadRateAvgYield9/30/2024Region1TOTAL LOANS0.03910400.05030750.08941159/30/2024Region1TOTAL DEPOSITS0.01950930.05237280.03286359/30/2024Region2TOTAL LOANS0.02468890.05108870.07577769/30/2024Region2TOTAL DEPOSITS0.02789040.05188050.023990212/31/2024Region1TOTAL LOANS0.03635060.04553670.081887312/31/2024Region1TOTAL DEPOSITS0.02765960.05743730.029777712/31/2024Region2TOTAL LOANS0.02294230.04941400.072356312/31/2024Region2TOTAL DEPOSITS0.02315050.04475520.02160473/31/2025Region1TOTAL LOANS0.00623190.04455370.05078563/31/2025Region1TOTAL DEPOSITS0.02690810.05462940.02772143/31/2025Region2TOTAL LOANS-0.00886390.04758730.03872333/31/2025Region2TOTAL DEPOSITS0.03272280.05127660.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:

DateOrganization HierarchiesPlanning HierarchiesSpreadRateAvgYield9/30/2024Region1TOTAL LOANS0.03910400.05030750.08941159/30/2024Region1TOTAL DEPOSITS0.01950930.05237280.03286359/30/2024Region2TOTAL LOANS0.02468890.05108870.07577769/30/2024Region2TOTAL DEPOSITS0.02789040.05188050.023990210/31/2024Region1TOTAL LOANS0.03910400.05030750.089411510/31/2024Region1TOTAL DEPOSITS0.01950930.05237280.032863510/31/2024Region2TOTAL LOANS0.02468890.05108870.075777610/31/2024Region2TOTAL DEPOSITS0.02789040.05188050.023990211/30/2024Region1TOTAL LOANS0.03910400.05030750.089411511/30/2024Region1TOTAL DEPOSITS0.01950930.05237280.032863511/30/2024Region2TOTAL LOANS0.02468890.05108870.075777611/30/2024Region2TOTAL DEPOSITS0.02789040.05188050.023990212/31/2024Region1TOTAL LOANS0.03635060.04553670.081887312/31/2024Region1TOTAL DEPOSITS0.02765960.05743730.029777712/31/2024Region2TOTAL LOANS0.02294230.04941400.072356312/31/2024Region2TOTAL DEPOSITS0.02315050.04475520.02160471/31/2025Region1TOTAL LOANS0.03635060.04553670.08188731/31/2025Region1TOTAL DEPOSITS0.02765960.05743730.02977771/31/2025Region2TOTAL LOANS0.02294230.04941400.07235631/31/2025Region2TOTAL DEPOSITS0.02315050.04475520.02160472/28/2025Region1TOTAL LOANS0.03635060.04553670.08188732/28/2025Region1TOTAL DEPOSITS0.02765960.05743730.02977772/28/2025Region2TOTAL LOANS0.02294230.04941400.07235632/28/2025Region2TOTAL DEPOSITS0.02315050.04475520.02160473/31/2025Region1TOTAL LOANS0.00623190.04455370.05078563/31/2025Region1TOTAL DEPOSITS0.02690810.05462940.02772143/31/2025Region2TOTAL LOANS-0.00886390.04758730.03872333/31/2025Region2TOTAL DEPOSITS0.03272280.05127660.0185538

It feels simple but I am struggling to execute.

Qiu · Answer

@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.

0424-mfrihart.yxzp

flying008 · Answer

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.

DateOrganization HierarchiesPlanning HierarchiesSpreadRateAvgYield2024-9-30Region1TOTAL LOANS0.0391040.05030750.08941152024-9-30Region1TOTAL DEPOSITS0.01950930.05237280.03286352024-9-30Region2TOTAL LOANS0.02468890.05108870.07577762024-9-30Region2TOTAL DEPOSITS0.02789040.05188050.02399022024-10-31Region1TOTAL LOANS0.0391040.05030750.08941152024-10-31Region1TOTAL DEPOSITS0.01950930.05237280.03286352024-10-31Region2TOTAL LOANS0.02468890.05108870.07577762024-10-31Region2TOTAL DEPOSITS0.02789040.05188050.02399022024-11-30Region1TOTAL LOANS0.0391040.05030750.08941152024-11-30Region1TOTAL DEPOSITS0.01950930.05237280.03286352024-11-30Region2TOTAL LOANS0.02468890.05108870.07577762024-11-30Region2TOTAL DEPOSITS0.02789040.05188050.02399022024-12-31Region1TOTAL LOANS0.03635060.04553670.08188732024-12-31Region1TOTAL DEPOSITS0.02765960.05743730.02977772024-12-31Region2TOTAL LOANS0.02294230.0494140.07235632024-12-31Region2TOTAL DEPOSITS0.02315050.04475520.02160472025-1-31Region1TOTAL LOANS0.03635060.04553670.08188732025-1-31Region1TOTAL DEPOSITS0.02765960.05743730.02977772025-1-31Region2TOTAL LOANS0.02294230.0494140.07235632025-1-31Region2TOTAL DEPOSITS0.02315050.04475520.02160472025-2-28Region1TOTAL LOANS0.03635060.04553670.08188732025-2-28Region1TOTAL DEPOSITS0.02765960.05743730.02977772025-2-28Region2TOTAL LOANS0.02294230.0494140.07235632025-2-28Region2TOTAL DEPOSITS0.02315050.04475520.02160472025-3-31Region1TOTAL LOANS0.00623190.04455370.05078562025-3-31Region1TOTAL DEPOSITS0.02690810.05462940.02772142025-3-31Region2TOTAL LOANS-0.00886390.04758730.03872332025-3-31Region2TOTAL DEPOSITS0.03272280.05127660.0185538

Gunnar-Scheck · Answer

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!

replicate data for missing dates.yxmd

Screenshot 2025-04-23 135938.png

Screenshot 2025-04-23 140248.png