Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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:
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.
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])) )
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:
Thank you very much! Great solution!
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |