Aggregate data based on a condition using a custom window size

  • 21 May 2024
  • 5 replies

Badge +1

 Hi Pigment community,

A similar topic has been opened before, the proposed solution was this:

However, not all the time I need the same amount of data to aggregate, since my metrics are variable (sometimes I would need to aggregate 10 values, sometimes 1), what its the possible workaround?

Tried using a formula like this: 
CUMULATE('Metric 1 to Aggregate'[FILTER : Month.'Start Date'<=(Month+'Metric 2 Lead Time Weeks').'Start Date' AND (Month+'Metric 2 Lead Time Weeks' + 'Metric 3 Lead Time Periods'-1).'Start Date'>= Month.'Start Date'],Month,"".SUM)

Trying to isolate only the months I want to aggregate, but it doesn’t works.
I tried to make and very detailed post but for whatever reason the topic wasn’t creating.


Tell me if you need more details. Thanks in advance,


Best answer by Issam Moalla 3 June 2024, 11:42

View original

5 replies

Badge +1

I need to create a Metric dimensioned by Months and Products (Let’s call it “Metric 4”), I want to aggregate another metric that it is also dimensioned by Months and Products but only aggregate it on the desired window determined by other 2 blocks that also has Month and Products


The desired behavior would be:
IF we looking at May 22:
May 22 = 0 
May 22 +1 = Jun 22 = 36
May 22 + 2 = Jul 22 = 37
May 22 + 3 = Aug 22 = 38

The final output on May 22 should be = 111 as you can see in the Metric 5 for Desired Output

The main reason that I can’t use MOVINGSUM it’s because that function requires scalar integers as End and Start Periods and removing Month and Product dimensions would cause the main issue to continue

Userlevel 5
Badge +9

Hi @A_Garneri ,

Currently the window size for the moving sum is not variable and need to be scalar.
However, there is a possible workaround to achieve this. In order to get to the final result my solution consists in applying an intermediate metric where I calculate the Cumulated values on the Metric to aggregate.

  • Cumulated value formula: 
    Cumulate('Metric 1 to Aggregate',Month)
  • Then the final metric (Metric 4) would look for the cumulated value based on the offset from Metric 3 Lead Time periods and Metric 2 Lead Time Weeks using the Shift function. and subtract this value from each months cumulated value. The formula would be as follows:
    ('Cumulated values'[BY CONSTANT: SHIFT(Month,'Metric 3 Lead Time Periods' + 'Metric 2 Lead Time Weeks'-1)] - 'Cumulated values')
    [FILTER: ISDEFINED('Metric 1 to Aggregate')]

    Where The [FILTER: ISDEFINED('Metric 1 to Aggregate')] is applied to only get the results where Metric 1 has values.


Hope this helps,

Badge +1

Hi @Issam Moalla,

Really appreciate your answer, this was the solution to my problem. You helped me a lot.


Badge +1

Just as side note, you should add “Shift” Function and any other that is not listed into this post:


Userlevel 5
Badge +9

Hi @A_Garneri ,

Thank you for the feedback, the SHIFT function has been added to the Functions by Category Post.