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