Integrate Limit in weekly Rates

[joshua1990]

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?

[daXtreme · Accepted solution]

@joshua1990 

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.

[tamerj1 · Accepted solution]

@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?

[tamerj1]

Hi @joshua1990 
This measure seems to work and splits the Orders into sets of 4. Please refer to attched file and below illustration

Total 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' ) ) )

[joshua1990]

@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'? 

[tamerj1 · Accepted solution]

@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?

[tamerj1 · Accepted solution]

@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

[daXtreme · Accepted solution]

@joshua1990 

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]

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?

[daXtreme]

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.

[tamerj1]

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.