Handling the missing dates

[gantaanvesh]

Hello Community,

I am facing an issue with previous missing data, let's say I have multiple products starting data available from 2017-04-01 but also I have added two more new products recently from 2019-05-01. so then except these two products remaining all the products I have the historic data as well.

So now I am sorting the data according to date and product, category and etc... to get last 12-month rolling value avg. data which has more than 12 months has no issues ... but then as I said I have added recently two more brands causing me the issue like (previous rolling 12 months am using row-1 condition, in this case, it is taking random values and giving me the incorrect result. 

so now what I need is I need to generate rows wherever brands do not contain 12 months I just need to generate rows and all the dimensions should same values should come  "0"

can someone help me with this logic?

I have tried by using generate tool with this condition " [Generate rows]>= DateTimeAdd(DateTimeFirstOfMonth(),-12,'months')"

"Datetimeadd([Generate rows],-1,'months')" it is generating but values also repeating the same for every month, which is not correct

[RolandSchubert · Accepted solution]

Hi @gantaanvesh ,

I think you used the first month as the initial value to generate rows. I've created a sample workflow, starting thew "generate rows" at [Current Month] - 1 and setting the value for this month to 0 before generate rows is executed. Does this help?

Best regards

Roland

[gantaanvesh]

Hi @RolandSchubert 

Thanks for the solution!!

I have a small doubt the example you gave has measures column only 1 what if I have 10 or more than 10 can i use data cleansing tool for selecting multiple columns and make it 0