cancel
Showing results for
Did you mean:
Microsoft

## 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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team

## Re: Burn-Up Chart for Project % Completion

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.

5 REPLIES 5
Super User IV

## Re: Burn-Up Chart for Project % Completion

@brianlehr what would be the calculation for %desired?

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Microsoft

## Re: Burn-Up Chart for Project % Completion

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.

Microsoft

## Re: Burn-Up Chart for Project % Completion

@parry2k and community -- any suggestions?

Highlighted
Community Support Team

## Re: Burn-Up Chart for Project % Completion

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.

Microsoft

## Re: Burn-Up Chart for Project % Completion

@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!

Announcements

#### Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!