cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Fill missing dates and skipping holidays and bufferdays

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

4 REPLIES 4
Community Support Team
Community Support Team

Re: Fill missing dates and skipping holidays and bufferdays

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?

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Re: Fill missing dates and skipping holidays and bufferdays

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

Re: Fill missing dates and skipping holidays and bufferdays

Anyone?
Highlighted

Re: Fill missing dates and skipping holidays and bufferdays

Please?

Helpful resources

Announcements
New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (1,840)