How can I change the result of this formula?

[zemagon]

Hi All,

     I need your help, If I want to subtract now, the result would be, for example, 10+9, and I would like to get the result 10-9.

Subtotal = VAR CurrentRowValue =
CALCULATE(
    SUM(Sheet4[Value]),
    FILTER(
        Sheet4,
        Sheet4[DuplicateCheck] = EARLIER(Sheet4[DuplicateCheck])
        && Sheet4[NewTimeColumn] = EARLIER(Sheet4[NewTimeColumn])
    )
)
RETURN
    CurrentRowValue

from this :

To This :

Best Regards,

Gon

[Daniel29195 · Accepted solution]

@zemagon 

use the measure below : 

Measure 2 = 
 var t =  MAX('Table'[time])
 var failed_step  = SELECTEDVALUE('Table'[Fail Step])


var ds = 
CALCULATETABLE(
    SUMMARIZE(
        'Table',
        'Table'[Fail Step],'Table'[time],'Table'[value]),
    'Table'[time]<=t,
    'Table'[Fail Step] = failed_step
)


var ds_w = 
    WINDOW(
        0,abs,1,REL,
        ds,
        ORDERBY('Table'[time], DESC),
        PARTITIONBY('Table'[Fail Step])
    )
    

var cacl     = 

    
SUMX(
    ds_w,
    SWITCH(
        TRUE(),
        'Table'[time] = t ,'Table'[value],
    'Table'[value] * -1 
    )
)

return cacl

let me know if this helps .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[Daniel29195]

@zemagon 

sorry, but i didnt get what you want to achieve ? 

can you please illustrate it with an example ? 
like what is the logic for the calculation you want to get ? 

[zemagon]

Dear @Daniel29195 
     I apologize for the insufficient information. I have data as shown below. I have data for 4 time ranges: (1AM, 7AM), (1PM, 8PM). These are Fail Step data for machine pairing operations. Since the 7AM data duplicates the 1AM data (and likewise for 1PM, 8PM), I want to subtract the values of matching time pairs with the same Fail Step. The formula I have written yields a sum instead of a subtraction. Do you have any suggestions on how I can achieve this?

If you need any additional information, please let me know.
Best Regards,
Gon

[Daniel29195 · Accepted solution]

@zemagon 

use the measure below : 

Measure 2 = 
 var t =  MAX('Table'[time])
 var failed_step  = SELECTEDVALUE('Table'[Fail Step])


var ds = 
CALCULATETABLE(
    SUMMARIZE(
        'Table',
        'Table'[Fail Step],'Table'[time],'Table'[value]),
    'Table'[time]<=t,
    'Table'[Fail Step] = failed_step
)


var ds_w = 
    WINDOW(
        0,abs,1,REL,
        ds,
        ORDERBY('Table'[time], DESC),
        PARTITIONBY('Table'[Fail Step])
    )
    

var cacl     = 

    
SUMX(
    ds_w,
    SWITCH(
        TRUE(),
        'Table'[time] = t ,'Table'[value],
    'Table'[value] * -1 
    )
)

return cacl

let me know if this helps .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[Ashish_Mathur]

Hi,

For this new table that you have shared, show the expected result in another column very clearly.

[zemagon]

Dear @Ashish_Mathur 

Here is the table of data with the columns Result I need. Thank you for your feedback.

time	Fail Step	value	DuplicateCheck	Answer
1am	Check Flow Rate	10	1	10
1am	Enable Bypass	5	0	5
1am	Get Calibration Data	15	0	15
7am	Check Flow Rate	18	1	8
7am	Reset Setup	3	0	3
1pm	Run10min	6	1	6
1pm	Enable Bypass	8	0	8
8pm	Buckmode	7	0	7
8pm	Run10min	7	1	1

Best Regards,

Gon

[Ashish_Mathur · Accepted solution]

Hi,

Use these calculated column formulas

Latest value prior to the one in the current row = LOOKUPVALUE(Data[value],Data[time],CALCULATE(MAX(Data[time]),FILTER(Data,Data[Fail Step]=EARLIER(Data[Fail Step])&&Data[time]<EARLIER(Data[time]))),Data[Fail Step],Data[Fail Step])

Diff = Data[value]-Data[Latest value prior to the one in the current row]

Hope this helps.

[Ashish_Mathur]

Share data in a format that can be pasted in an MS Excel file.  An image does not help.