Forum Discussion

brianlehr's avatar
brianlehr
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago
Solved

Burnup Chart Based on Known Completion Dates

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 DateFinished
A1/15/20201
B1/15/20200
C1/15/20200
D1/15/20200
E1/15/20200
A1/17/20201
B1/17/20200
C1/17/20200
D1/17/20200
E1/17/20201
A1/19/20201
B1/19/20200
C1/19/20200
D1/19/20200
E1/19/20201

 

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 DateFinished
B1/21/20201
C1/23/20201
D1/23/20201

 

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

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    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.

     

2 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      brianlehr
      Icon for Microsoft Employee rankMicrosoft Employee
      6 years ago

      This is perfect.  It's the EARLIER that always screws up my thinking. ğŸ˜€