How to sum values for each record belongs ti time interval

[szmilosz]

Hi there,

I would like to sum up the Qty values for each MaterialNumber, grouping them by DateToCalculate + LeadTime (weeks).

I mean summing up all the records with the same MaterialNumber for each record, where their DateToCalculate is less than DateToCalculate + LeadTime (number of weeks).

Is it possible to do this in a new column?

I kindly ask for your assistance.

[Anonymous · Accepted solution]

Hi @szmilosz ,

You can create a measure or a calculated column.

Measure:

Measure =
CALCULATE (
    SUM ( 'Table'[ZEM33.Qty] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        [ZEM33.MaterialNumber] = MAX ( 'Table'[ZEM33.MaterialNumber] )
            && [LeadTime] = MAX ( 'Table'[LeadTime] )
            && [DateToCalculate] = MAX ( 'Table'[DateToCalculate] )
    )
)

Calculated column:

Column = 
CALCULATE (
    SUM ( 'Table'[ZEM33.Qty] ),
    FILTER (
          'Table' ,
        [ZEM33.MaterialNumber] = EARLIER(  'Table'[ZEM33.MaterialNumber] )
            && [LeadTime] = EARLIER(  'Table'[LeadTime] )
            && [DateToCalculate] = EARLIER(  'Table'[DateToCalculate] )
    )
)

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.