Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Specific running total column - next n rows per level

Hi I need to include per row sum of column for next 12 indexs, but I have problem with my formula. 
Somebody know the solution? 
See me screen with description of result I need - probably need to fix conditionals but don't have idea how to do it.

Demand 13 Column =
CALCULATE(sum(DemandWeekly[DemandWeekly]),
                ALLEXCEPT(DemandWeekly,DemandWeekly[KeyDemandWeekly],DemandWeekly[ProductCode]),
                DemandWeekly[GroupIndex]>=EARLIER(DemandWeekly[GroupIndex]) && DemandWeekly[GroupIndex]<DemandWeekly[GroupIndex]+12  )



 

Need Help
  • MarkLaf's avatar
    MarkLaf
    3 years ago

    The relatively new WINDOW function is probably the best approach for this. 

    Demand 13 Column = 
VAR _window = 
    WINDOW(
        0, 12,
        ORDERBY( DemandWeekly[GroupIndex], ASC ),
        PARTITIONBY( DemandWeekly[KeyDemandWeekly], DemandWeekly[ProductCode] ) 
    )
RETURN
CALCULATE( 
    SUM( DemandWeekly[DemandWeekly] ), 
    REMOVEFILTERS( DemandWeekly ),
    _window  
)

1 Reply

  • MarkLaf's avatar
    MarkLaf
    Icon for Super User rankSuper User
    3 years ago

    The relatively new WINDOW function is probably the best approach for this. 

    Demand 13 Column = 
VAR _window = 
    WINDOW(
        0, 12,
        ORDERBY( DemandWeekly[GroupIndex], ASC ),
        PARTITIONBY( DemandWeekly[KeyDemandWeekly], DemandWeekly[ProductCode] ) 
    )
RETURN
CALCULATE( 
    SUM( DemandWeekly[DemandWeekly] ), 
    REMOVEFILTERS( DemandWeekly ),
    _window  
)