Fill missing dates and skipping holidays and bufferdays

[richard-powerbi]

I'm breaking my head over this one. I hope someone is clever enough to help me..:)

Note:
- For the example below I'm using numbers instead of dates. I thought a number would be easier for the eye.
- Table1 is sorted.
- Both Category and Product influence how the number changes downwards.
- Both Bufferdays and Holidays influence how the number changes downwards.
- Holidays should always be skipped. Bufferdays should only be skipped when there is a change in Product.
- Within the same Product, when there are no Holidays, +1 is the default change downwards.
- To determine what is the next number downwards, first look if the next number is in Holidays, if so then skip that number.
A holiday doesn't count as a buffer, so after checking Holidays always add Bufferdays, but only add them when there is a change in Product.
- When for example '20' isn't filled at row 'Y;A03', then then Table1 (desired result v2) should be the desired result.
In other words, the filling down should only be within each Category when a 'start value' is available for that Category.
- Imagine if there was a category 'Z' with a 'start value' available, then it should do all the stuff for that category after having skipped category 'Y'.

Parameter Bufferdays:
Bufferdays = 2

Table Holidays:
Number
8
9
10
21
26
27
28

Table Table1 (starting point):
Category;Product;Number
X;A01;1
X;A01;null
X;A02;null
X;A02;null
X;A03;null
X;A03;null
Y;A03;20
Y;A03;null
Y;A05;null
Y;A05;null

Table Table1 (desired result v1):
Category;Product;Number
X;A01;1
X;A01;2
X;A02;05
X;A02;06
X;A03;12
X;A03;13
Y;A03;20
Y;A03;22
Y;A05;25
Y;A05;29

Table Table1 (desired result v2)
Category;Product;Number
X;A01;1
X;A01;2
X;A02;5
X;A02;6
X;A03;12
X;A03;13
Y;A03;null
Y;A03;null
Y;A05;null
Y;A05;null

[v-frfei-msft]

Hi @richard-powerbi ,

Based on my test, I cannot achieve your goal by your data. Is there any date columns in your table? Btw, for Parameter Bufferdays, is that a Parameter table you have created in power query  or a what if parameter in data model?

[richard-powerbi]

Hi @v-frfei-msft , thanks for looking into it.

The number column is actually a dates column. I thought numbers would be easier for the eye. It shouldn't matter fot the method of solving this.

Parameter Bufferdays is a parameter in Power BI.

I also have a dates table with all the occuring dates in Table1, if that is of any help...

The goal is pretty straightforward. Maybe this sums it better:

• Fill the missing dates (numbers in this example)
• Always +1 within a Product series
• Always +Bufferdays on a Product change
• Always skip holidays
• Don't fill a Product series when there is no start value

[richard-powerbi]

Anyone?

[richard-powerbi]

Please?