sum rows based on condition

[kadapavel]

Hi, can someone help me to make sum calculation of each heat up sequence, shown on exapmle. In excel I can make reference to previous cell of same column I calculate, here I managed only to refer to earlier values in another columns, but that does not help. 

Other option I considered is to name each heat up secuence and then i could use something like this 

=CALCULATE(SUM([PrevTemp]), 
           FILTER(ALL(‘Table’), 
             ‘Table’[NameOfHeatUpSeq]=EARLIER(‘Table’[NameOfHeatUpSeq])
                 )
          )

But was not able to give new name to each sequence...

Thanks in Advance

[ofirk]

Hi @kadapavel,

I think you could use the following column (with your table instead of Table1):

Sum of Each Heat Up = SUMX(FILTER(Table1, [index] <= EARLIER([index])), [Prev Temp])

These are the results I get when I use some of your data:

If you don't want to use the index column, you can probably use your timestamp column.

[kadapavel]

Hi, thanks for reply, It almost worked. I need to separate sum calculation for each sequence. Target is to calculate cumulative of each heating.

I 

I made new column and modified your DAX a bit, but still there is some issue...

Testsc = 
var Blank1 = calculate(max('rec METEO'[Prev Temp]), FILTER('rec METEO', 'rec METEO'[Prev Temp]), 'rec METEO'[Index]=earlier('rec METEO'[Index])-1)
return
if(ISBLANK('rec METEO'[Prev Temp]),BLANK(),
    if(ISBLANK(Blank1),'rec METEO'[Prev Temp],
                        SUMX(FILTER('rec METEO', [index] <= EARLIER([index])), [Prev Temp])) )

[ofirk · Accepted solution]

Hi @kadapavel,

Sorry I missed that.

Can you try this:

1. Create a column that saves the index of the last row before heating begins:

Heat Start = 
IF([Prev Temp]>0, 
    MAXX(
        FILTER(Table2, 
                [Index] < EARLIER([Index]) && 
                [Prev Temp] = 0), 
        [Index]))

2. Use the following calculation:

Sum of Each Heat Up = 
IF([Prev Temp] > 0, 
    SUMX(
        FILTER(Table2, 
                [Index] <= EARLIER([Index]) && 
                [Index] >= EARLIER([Heat Start])), 
        [Prev Temp]))

I believe it works:

[kadapavel]

Thank you very much! Great solution!