Date issue due to no data

Hello all,

I've created a chart with inventory levels. I want to get a clear view on the inventory development troughout the years. However the line in my chart is fluctuating a lot and isn't reperesenting the total inventory on hand. This issue is occuring because i'm missing some dates in my data. I've explained the problem more in detail in de attachement.

I hope somebody can help!

-Tom

Hello,
I did it with Power Query. The converted data is not returned to the worksheet but is used only as a source for the pivot table. It looks better (according to me) as we group dates to years and months.

Hi Stasinek,

Thanks for your reply. However, by grouping the data to years and dates, you sum up all KG's that are in stock. This creates an image that isn't correct. E.g. the highest stocklevel of the month is 10.000KG, but because we grouped all dates in the month, we get a stocklevel of 100.000KG. Know what i mean?

Any other solutions?

I'm trying to figure out how V.Lookup, Indirect and IFNA functions could help to fill up all cells if i would manually create dates from 1-1-2016 to 1-7-2018. Can't get it to work though.

This may be a slightly different solution