Forum Discussion
Integrate Limit in weekly Rates
Hi all!
I have a table that shows me per order nr a date:
| Order Nr | Value | Date |
| A1 | 500 | 01.01.2022 |
| A2 | 600 | 03.01.2022 |
Then in a bar chart I display the value for each week.
What I would like to add now is a limit of 5 orders per week. If the number of orders is above 5, then this should be rolled up to the next week and so on.
How is that possible in DAX as a calulcated column?
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
8 Replies
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 joshua1990
This measure seems to work and splits the Orders into sets of 4. Please refer to attched file and below illustrationTotal Value = VAR SelectedWeeks = CALCULATETABLE ( 'Date', ALL ( 'Date' ), ALLSELECTED ( 'Date'[Week Number] ) ) VAR Ranking = RANKX ( SelectedWeeks, CALCULATE ( MAX ( 'Date'[Week Number] ) ),, ASC, Dense ) VAR CurrntOrders = VALUES ( Orders[Order Nr] ) VAR PreviousOrders1 = SELECTCOLUMNS ( TOPN ( Ranking * 4, ALL ( Data ), [Index], ASC ), "@Order", [Order Nr] ) VAR PreviousOrders2 = SELECTCOLUMNS ( TOPN ( ( Ranking - 1 ) * 4, ALL ( Data ), [Index], ASC ), "@Order", [Order Nr] ) VAR T1 = EXCEPT ( PreviousOrders1, PreviousOrders2 ) VAR T2 = INTERSECT ( CurrntOrders, T1 ) RETURN SUMX ( T2, CALCULATE ( SUM ( Data[Value] ), ALL ( 'Date' ) ) )tamerj1 : Awesome, thanks a lot! I know, it is a bit different, but is there any chance to get the new date per order in a calculated column in table 'Data'?
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?
