Forum Discussion
Integrate Limit in weekly Rates
Too little info. Please create a more detailed description of what you want. A picture would be worth creating. You can make such a picture in Excel. Create a table or tables of what the input is, then the output you want and copy the tables as pictures. Then paste in here so people have a good understanding of your requirement.
Thanks.
joshua1990
I though that you wanted to have a dynamic solution as per below screenshot.Anyhow, Please confirm whcih columns are the oriogional ones? Which columns you've already added? Did you add them as calculated columns or using power query? Can you add columns using power query?
joshua1990
One way to create the required calculated column is to have an index column starting from 0 can be easily created using power query and if not can be created using dax
Too little info. Please create a more detailed description of what you want. A picture would be worth creating. You can make such a picture in Excel. Create a table or tables of what the input is, then the output you want and copy the tables as pictures. Then paste in here so people have a good understanding of your requirement.
Thanks.
Hi daXtreme : Thanks. Below you can find additional information:
| Order Nr | Value | Date | Max 4 Order per Week | Rank Order per Week | Week to be Counted |
| A1 | 500 | 02.09.2022 | TRUE | 1 | 35 |
| A2 | 600 | 03.09.2022 | TRUE | 2 | 35 |
| Ae | 400 | 03.09.2022 | TRUE | 3 | 35 |
| Ag | 600 | 03.09.2022 | TRUE | 4 | 35 |
| Ae | 500 | 04.09.2022 | FALSE | 5 | 36 |
| Bs | 300 | 04.09.2022 | FALSE | 6 | 36 |
| C4 | 600 | 05.09.2022 | TRUE | 1 | 36 |
| FF | 500 | 05.09.2022 | TRUE | 2 | 36 |
| GR | 300 | 05.09.2022 | FALSE | 3 | 37 |
There are weeks with multiple orders. The maximum ist 4 order per week. It this limit is reached, then this order has to be counted in the next week. And so on.
I don't know if this is the right approach, but I added a ranking column that ranks the orders per week from 1-4. Then I added a binary column with true or false to show if it is counted for this week order next week.
And in the last one we have the final week number.
Is this possible in DAX? Like a roll over effect of current backlog of orders?
I'm not right now sure if it's possible in DAX. I'm not even sure you should do it in DAX. But I'm almost sure you should do it in Power Query because this is the tool for such jobs. In DAX it may not be possible because it very much looks like a recursive-ish calculation and recursion is not supported in DAX. But is easy in Power Query.
Hi daXtreme
My understanding is that joshua1990 is looking for a dynamic solution which cannot be achieved using Power Query. Not sure if he is aware how complex can that get. Still there might be a chance to achieve it but even if it works, not sure if it will be efficient enough.