Populating last months data for items with missing value

[Anonymous]

Hello, 

Can someone assist me. 

I attached a file of data below. 

Ultimately I am looking for the EOM balance for certain items where I can then break down the data by month afterwards.  I have managed to get the values for the EOM but there are some gaps, ex. say we use the table below. 

Item	Month	"Accumulated"
1	January 2022	5
1	March 2022	3
1	April 2022	1

Here there would be no value for February.  The goal is to have it included with the previous months value. 

Item	Month	"Accumulated"
1	January 2022	5
1	February 2022	5
1	March 2022	3
1	April 2022	1

You can see the actual chart on my file.

Sample 

[Anonymous]

Update: 

Thank you for your reply.

I created a crossjoined table for the items and all dates.

I understand the importance of the crossjoined table. With a date for each day for each item. In theory then all of the transactions would appear with each date = 0 for dates with no requirement.

But this is giving me very odd values for the AccumulatedOnDate column.

You can see the table on the left has the correct accumulated values but on the right the accumulated on date is so far off I don't understand it.

The dates and the DatetoUse are working but not exactly, for the item shown in the table snapshot there shouldn't be any values until aug 2022. 

Can you help me pull the right data values for Accumulate on date?

Link to tables image 

Link to test file 

[v-xiaotang]

Hi @Anonymous 

Thanks for reaching out to us.

One idea, in this case, you have to create a secondary table containing all the months, and then calculate the February value in the auxiliary table. When calculating the value of February, you need to use the if() function to determine it, and if it is blank, get the value of January and assign the value to February.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.