I have 2 tables - 1) Invoice table 2) Date. They are connected by Date as below.
And there are more than 100s of products in the "Invoice Table". And some products may not have sales in some months.
Since there are lot of products the BILL_DATE has all dates ( missing some for some products). The reports are good. Now I am trying to do Rolling 12 Months calculation for each product. I am using the below formula. It gives the correct R12 value if there is no missing months. But if there is missing months it looks for 12 sales months instead of last 12 months.
1) You many need to fill the missing dates in your invoice table so that you will have all the Dates for each product and then your expression will return the correct value. See the below links for this option
Thanks for your response Kush. But none of them are working. Already Bill Date has all dates. Since there are more than 100s of products there is a sale on each date. The problem is I am trying to do R12M calculation by each product which may not have sales in all months. This is what I am getting.
Kush is right that you will have to create the date or a 0 value sale for each product/Date. Recently had to do this with sales and unit closures during covid in order to get a proper rolling average. Was the same issue where I had to create a date point and a sale point, even if it is now a 0, for each individual Unit so that I could roll that up. We did this using a master calendar to create any missing dates and then replaced any null sales points with a 0.