Burnup Chart Based on Known Completion Dates

[brianlehr]

Hi all, trying to do something which I think is relatively simple but I can't wrap my mind around it.

• I'm already using a countrows measure to determine current % completion
• I want to use a similar function based on future dates to create a burnup chart

These are two sets of data, and then I want to combine into one chart.  An example is below.

Set 1 -- this is historical data, but refreshed automatically so on 1/21 I'll get new data that looks similar (ideally with more sites finished)

Site	Date	Finished
A	1/15/2020	1
B	1/15/2020	0
C	1/15/2020	0
D	1/15/2020	0
E	1/15/2020	0
A	1/17/2020	1
B	1/17/2020	0
C	1/17/2020	0
D	1/17/2020	0
E	1/17/2020	1
A	1/19/2020	1
B	1/19/2020	0
C	1/19/2020	0
D	1/19/2020	0
E	1/19/2020	1

I use the measure below to get me percentage done so far.  It shows me 20% for 1/15 and 40% for 1/17 and 1/19.

% Complete =

VAR CompleteCount = COUNTROWS(FILTER(Set1,Set1[Finished]=1))

VAR OverallCount = CALCULATE ( COUNTROWS(Set1))

RETURN

    DIVIDE(CompleteCount,OverallCount)

The second set of data is manual, it's me using estimated completion dates for the remaining sites.  Note I do not have the other sites included in this data for each date or I could just use the same measure.  While I could do this, the numbers are much larger in the real data and it becomes unwieldy.

Site	Date	Finished
B	1/21/2020	1
C	1/23/2020	1
D	1/23/2020	1

What can I use for a measure that would show me 60% on 1/21 and 100% on 1/23, all on the same chart?  Note if I combine the data into one table I have to be careful of future "real" data in Set 1 conflicting with the manual data in Set 2.  Also I have the date as a shared CALENDARAUTO() table as shown below.

 

Thanks in advance!!

[v-frfei-msft · Accepted solution]

Hi @brianlehr ,

I have created a sample for your reference, please check the following steps as below.

1. Create a calculated table like that.

Table = CROSSJOIN(DISTINCT('Set 1'[Site ]),UNION(DISTINCT('Set 1'[Date]),DISTINCT('Set 2'[Date])))

2. Based on that, create the two calculated columns as below.

Column = 
VAR dat = [Date]
VAR s = [Site ]
RETURN
    LOOKUPVALUE (
        'Set 1'[Finished],
        'Set 1'[Date], [Date],
        'Set 1'[Site ], [Site ]
    )
        + CALCULATE (
            MAX ( 'Set 2'[Finished] ),
            FILTER ( 'Set 2', 'Set 2'[Date] = dat && 'Set 2'[Site ] = s )
        )

Column 2 = 
CALCULATE (
    MAX ( 'Table'[Column] ),
    FILTER (
        'Table',
        'Table'[Site ] = EARLIER ( 'Table'[Site ] )
            && 'Table'[Date] <= EARLIER ( 'Table'[Date] )
    )
)

3. Then we can get the excepted result by this measure.

Measure = DIVIDE( CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Column 2]=1)),CALCULATE(COUNTROWS('Table')))

For more details, please check the pbix as attached.

[brianlehr]

This is perfect.  It's the EARLIER that always screws up my thinking. 😀