Gap filling all months for each category

[jbx999]

I have a Stock table which represents changes to stock levels for each product every month.

It looks something like this:

Product_ID	Description	Year-Month	Quantity_Change	Quantity_RT (Measure Column)
1	Product 1	2018-01	100	100
1	Product 1	2018-02	120	220
2	Product 2	2018-01	50	50
2	Product 2	2018-05	-20	30
1	Product 1	2018-06	-150	70
2	Product 2	2020-10	-10	20
1	Product 1	2020-12	40	110

Note that the last column was added as a running total in DAX. 

I want to plot the stock levels every month for each product. If I try to plot this table (Year-Month on the x-axis and Quantity_RT on the y-axis) I get a lot of gaps and the data will stop at the last month where there was an update. 

I have a CalendarTable which has all the Year-Month entries, from the earliest date till `Today()`, which is linked via a relationship already using the Year-Month field.  

Is there a way to gap fill all months for each product (using DAX since I need to use the running total column)? For each product and year-month pair, if there is no entry, I want to use the value of the previous month (since it means that for that product there was no change). 

If there is no previous value (it is the first time the product appears), I can either leave it out, or set it to 0, it doesn't matter, whatever is easiest. 

So the result I want is the following:

Product_ID	Description	Year-Month	Quantity_Change	Quantity_RT (Measure Column)
1	Product 1	2018-01	100	100
1	Product 1	2018-02	120	220
1	Product 1	2018-03	0	220
1	Product 1	2018-04	0	220
1	Product 1	2018-05	0	220
1	Product 1	2018-06	-150	70
...
1	Product 1	2020-11	0	70
1	Product 1	2020-12	40	110
2	Product 2	2018-01	50	50
2	Product 2	2018-02	0	50
2	Product 2	2018-03	0	50
2	Product 2	2018-04	0	50
2	Product 2	2018-05	-20	30
...
2	Product 2	2020-09	0	30
2	Product 2	2020-10	-10	20

All going up to the current month. The rows in italics show the gap filled ones that were added. 

I was thinking of trying to do some outer join between the CalendarTable and the Stock table to generate all the month-product pairs, but not sure how to go about this to use the previous value. 

Any idea how to go about this?

[amitchandak]

@jbx999 , If you are using running total using date tbale , then and using moth year from date table on axis, you should get the month with missing

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected('Date'),'Date'[date] <=max('Date'[date])))

or

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

use column from date table on axis for month, date qtr, year etc

[jbx999]

The running total is using the Year-Month, but it has gaps. There are some months where there was no update to the quantity. I want to "gap fill" them so that the running total does not have gaps. 

My current Quantity_RT is calculated as follows already:

```

Quantity_RT = sumx(filter('Stock',

[Product_ID] =earlier([Product_ID]) && [Year-Month] <= EARLIER([Year-Month])), [Quantity])

```

I think I can either fill in the gaps before, with Quantity = 0 for those missing months, or fill them afterwards. But I can't understand how your solution fits. 

[v-easonf-msft · Accepted solution]

Hi, @jbx999 

I create a sample pbix file for you reference.

You need to add a new column 'YM' in a new Calendar table:

YM = FORMAT('Calendar'[Date],"YYYY-MM")

Then add a new measure as below to calculate the value:

Quantity_RT = CALCULATE(SUM('Table'[Quantity_Change]),FILTER('Table','Table'[Year-Month]<=MAX('Calendar'[Date])))

Best Regards,
Community Support Team _ Eason