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% Desired5/3/201916.10% 5/5/201916.10% 5/6/201917.50% 5/8/201917.50% 5/10/201917.50% 5/12/201918.90% 5/15/201918.90% 5/16/201919.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?

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 RegardsMaggie Community Support Team _ Maggie LiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

brianlehr

Microsoft Employee

7 years ago

Solved

Burn-Up Chart for Project % Completion

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?

Need Help

v-juanli-msft

7 years ago

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.

burn-up.pbix50 KB

5 Replies

v-juanli-msft

Community Support

7 years ago

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.

burn-up.pbix50 KB

brianlehr
Microsoft Employee
7 years ago
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
Super User
7 years ago
brianlehr what would be the calculation for %desired?
- brianlehr
  Microsoft Employee
  7 years ago
  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
    Microsoft Employee
    7 years ago
    parry2k and community -- any suggestions?

Forum Discussion

Burn-Up Chart for Project % Completion

5 Replies

Related content

Projects Created Vs Projects Completed

Project percent complete

Line Chart - Project Created vs Project Completed

Percentage of Completed Projects

Project SChedule Completion %