Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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.
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.
@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!
@brianlehr what would be the calculation for %desired?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |