Subtract the value from 1st step when its not blank

[edubcardoso]

Hello everyone!

I'm struggling with this calculation.
My goal is to get the sum off the value, but it has a twist:

1) I want to subtract the 1s step to the next one with a value, but in the same project.

For the project 17651, i want to subtract the 1st step in the 3rd(because it is the next with a value, 2nd step is blank)
For the project 45521, it should subtract step 1 to step 2.

Project |Step |        |     Value     |      Goal

17651	1		13 186,29 €	13186,29
17651	2			0
17651	3		100 062,77 €	100062,77-13186,29
19929	1			0
19929	2		16 076,69 €	16076,69
19929	3		39 756,08 €	39756,08
19929	4			0
19929	5		37 660,47 €	37660,47
45521	1		10 047,6 €	10047,60
45521	2		12 196,3 €	12196,30-10047,60
45521	3		16 795,44 €	16795,44
45521	4		23 589,9 €	23589,90

Can you help me, please?

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

Value measure: = 
SUM( Data[Value] )

Goal measure: =
VAR _currentproject =
    MAX ( Data[Project] )
VAR _minstepaftersteptwo =
    MINX (
        FILTER (
            ALL ( Data ),
            Data[Project] = _currentproject
                && Data[Step] > 1
                && Data[Value] <> BLANK ()
        ),
        Data[Step]
    )
VAR _steponevalue =
    CALCULATE ( [Value measure:], Data[Step] = 1 )
RETURN
    IF (
        HASONEVALUE ( Data[Project] ),
        SWITCH (
            TRUE (),
            [Value measure:] = BLANK (), 0,
            MAX ( Data[Step] ) = 1, [Value measure:],
            MAX ( Data[Step] ) = _minstepaftersteptwo, [Value measure:] - _steponevalue,
            [Value measure:]
        )
    )