Generate rows dynamically for month

[Shank]

HI all,

I have a set of data where i need to generate a row if no data available for  that particular month. below is my input and output required.

i have a set of data which has to generate the row dynamically for next 12 month and where ever we dont have data just need to show count as 0.

in below case we have Calendar day whcih reads as August 2018 (8.2018).

next month it will change to 09.2018 to 08.2019 so i need to set it dynamcially to generate the row can anyone help on this?

Input

Code	Name	Loc	Calendar Day	Count
1	A	427	8.2018	160 KG
1	A	427	9.2018	100 KG
1	A	427	10.2018	200 KG
1	A	427	12.2018	100 KG
1	A	427	1.2019	100 KG
1	A	427	2.2019	200 KG
2	B	427	8.2018	100 KG
2	B	427	9.2018	200 KG
2	B	427	10.2018	60 KG
2	B	427	11.2018	160 KG
2	B	427	12.2018	480 KG
2	B	427	1.2019	20 KG
2	B	427	2.2019	20 KG
2	B	427	3.2019	20 KG
2	B	427	4.2019	20 KG
2	B	427	5.2019	20 KG
2	B	427	6.2019	20 KG
2	B	427	7.2019	20 KG
3	C	427	11.2018	23 KG
3	C	427	12.2018	115 KG
3	C	427	1.2019	113 KG
3	C	427	2.2019	112 KG
3	C	427	3.2019	121 KG

Output

Code	Name	Loc	Calendar Day	Cout
1	A	427	8.2018	160 KG
1	A	427	9.2018	100 KG
1	A	427	10.2018	200 KG
1	A	427	11.2018	0 KG
1	A	427	12.2018	100 KG
1	A	427	1.2019	100 KG
1	A	427	2.2019	200 KG
1	A	427	3.2019	0 KG
1	A	427	4.2019	0 KG
1	A	427	5.2019	0 KG
1	A	427	6.2019	0 KG
1	A	427	7.2019	0 KG
2	B	427	8.2018	20 KG
2	B	427	9.2018	200 KG
2	B	427	10.2018	60 KG
2	B	427	11.2018	160 KG
2	B	427	12.2018	480 KG
2	B	427	1.2019	20 KG
2	B	427	2.2019	20 KG
2	B	427	3.2019	20 KG
2	B	427	4.2019	20 KG
2	B	427	5.2019	20 KG
2	B	427	6.2019	20 KG
2	B	427	7.2019	20 KG
3	C	427	8.2018	0 KG
3	C	427	9.2018	0 KG
3	C	427	10.2018	0 KG
3	C	427	11.2018	23 KG
3	C	427	12.2018	115 KG
3	C	427	1.2019	113 KG
3	C	427	2.2019	112 KG
3	C	427	3.2019	121 KG
3	C	427	4.2019	0 KG
3	C	427	5.2019	0 KG
3	C	427	6.2019	0 KG
3	C	427	7.2019	0 KG

[BenMoss · Accepted solution]

There's a quite simple solution to this and it uses the 'generate rows' tool.

Here we can use the datetimetoday() function to acknowledge the current month and then use the generate rows tool to get the 12 months that should be in our data stream for each group.

You can then do an append and a join to bring calculate what months are missing.

Then a union to bring your data together into the output.

See attached solution.

Ben