Drawdown Curve

[hackfifi]

Good Day

I am trying to calculate the "Drawdown" row as per the below data table example.
As you can see the TOTAL CUMATIVE Formula used gives the Cumulative by Year resulting in a value of $186

I am trying to Acheive the "DrawDown" calculation based on WIN(+) and NO WIN(-) by year

Hence starting from $186 (Total Budget) - "NO WIN" + "WIN" 

So in the below example:

=186-[5+0]-[25+0]-[15+0]-[5+16]-[15+10]-[30+0]-[25+15]-[15+10]

Hence, i need support to develop the above measure Cumulative Drawdown by Year

Thanks!

Total_Cumulative_Overall =

CALCULATE(

    [Budget],

    FILTER(

        ALLSELECTED('_Calendar'),

        ISONORAFTER(_Calendar[Date], MAX(_Calendar[Date]),DESC)

        )
)

[Anonymous · Accepted solution]

Hi @hackfifi ,

My steps are as follows:

1. Make a copy in the PowerQuery Eidtor and name it Table1:

2.Replace Values for column 2017-2026:

3.Unpivot column for column 2017-2026:

4.Enter data-> Table2:

5.New measures:

Burget = SUM('Table1'[Value])

Total_Cumulative_Overall = 
CALCULATE (
    [Burget],
    FILTER ( ALL ( 'Table1' ), 'Table1'[Year] <= MAX ( 'Table1'[Year] ) )
)

win = 
CALCULATE (
    SUM ( 'Table1'[Value] ),
    FILTER (
        ALL( 'Table1' ),
        'Table1'[WIN STATUS] = "WIN"
            && 'Table1'[Year] <= MAX ( 'Table1'[Year] )
    )
)

no win = 
CALCULATE (
    SUM ( 'Table1'[Value] ),
    FILTER (
        ALL( 'Table1' ),
        'Table1'[WIN STATUS] = "NO WIN"
            && 'Table1'[Year] <= MAX ( 'Table1'[Year] )
    )
)

Drawdown = 
VAR _all = CALCULATE([Total_Cumulative_Overall],ALL())
VAR _result = _all - [no win] + [win]
RETURN
_result

Measure = 
SWITCH(
    SELECTEDVALUE('Table2'[Colunm]),
    "Total_Cumulative_Overall",[Total_Cumulative_Overall],
    "Drawdown",[Drawdown]
)

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data