Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
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?
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:
Please?