Burn-Up Chart for Project % Completion

[brianlehr]

Hi everyone,

Trying to do something that probably isn’t all that difficult but for whatever reason isn’t coming naturally to me.

I’d like to create a “burn-up” chart to show the rate required to achieve 100% completion of all work items by a specified date.  Some example data, assuming I get a reading once every 2 days or so (and it doesn’t start at 0 because I didn’t start gathering data until after it was in motion).

Date	% Completed	% Desired
5/3/2019	16.10%
5/5/2019	16.10%
5/6/2019	17.50%
5/8/2019	17.50%
5/10/2019	17.50%
5/12/2019	18.90%
5/15/2019	18.90%
5/16/2019	19.50%
5/18/2019		23.1%
5/20/2019		26.82%
5/22/2019		30.48%
5/24/2019		34.14%

And let’s just say I had to get to 100% by 6/30.  Would want to project a column/measure that showed the % complete required on each day (basically, the rate required) to make that goal.  I’m not sure if the numbers I have there are exactly right, but hopefully you get the idea.  Note that currently the % completed in my chart is a calculated measure.  Any suggestions?

[v-juanli-msft · Accepted solution]

Hi @brianlehr 

1.Create a date table

Date table = CALENDARAUTO()

Create relationship as below

2. in Sheet3, create measures as below

lastdate = MAXX(FILTER(ALL(Sheet3),[% Completed]<>BLANK()),Sheet3[Date])

desireddate = DATE(2019,6,30)

period = DATEDIFF([lastdate]+1,[desireddate],DAY)/2

last percent = 
CALCULATE([% Completed],FILTER(ALL(Sheet3),Sheet3[Date]=[lastdate]))

rate = (1-[last percent])/[period]

start = IF(MAX('Date table'[Date])=[lastdate]+2,[last percent]+[rate])

data_modified =
VAR num =
    DATEDIFF ( [lastdate] + 2, MAX ( 'Date table'[Date] ), DAY ) / 2
RETURN
    IF (
        num = INT ( num )
            && MAX ( 'Date table'[Date] ) <= [desireddate],
        IF ( MAX ( 'Date table'[Date] ) = [lastdate] + 2, [start], [rate] ),
        BLANK ()
    )

% Desired =
VAR desireddata =
    SUMX (
        FILTER (
            ALL ( 'Date table' ),
            'Date table'[Date] >= [lastdate] + 2
                && 'Date table'[Date] <= [desireddate]
                && 'Date table'[Date]
                    <= MAX ( 'Date table'[Date] ) + 1
        ),
        [data_modified]
    )
RETURN
    IF ( [data_modified] <> BLANK (), desireddata, BLANK () )

final = IF([% Desired]=BLANK(),[% Completed],[% Desired])

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[brianlehr]

@v-juanli-msft This is great!  Usually I'm able to follow along, but I will admit that some of this lost me on first glance, but it seems to work.  I'll need to study this more to understand.  Thanks!

[parry2k]

@brianlehr what would be the calculation for %desired?

[brianlehr]

Sorry, not sure I understand the question?   The % desired is what I want to generate as a "burnup".

So to get the sample numbers shown there, I worked out what the % required increase for every data measurement period would be.

I assume 6/30 which was 22 "2-day periods" from the last date with data, so I think you'd need about 3.6% per each period to reach 100% by 6/30.

[brianlehr]

@parry2k and community -- any suggestions?