Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
52 | |
21 | |
12 | |
11 |