Burndown chart for data progress

[boclifton]

I've found a few instances of using a burndown chart in TFS and Scrum settings, but my team has an existing report in SSRS that I'm trying to remodel in Power BI that utilizes a burndown chart for project progress for data migration.  I have several projects in a Projects table, and all the transaction data in a Transactions table for migrated data.  This leads me to two questions:

1) I have the expected number of items in the Projects table and the total number of items completed in the Transactions table.  Do I need to create a new table with calculated data? If so, will this account for slicing to different projects?

2) I also need to show the estimated date of completion based on an average of the Actual data migrated over the past 2 weeks.  How will I calculate this? 

Thanks in advance for any help.  I'm still pretty new to Power BI so a lot of this escapes me for now.  I've included an example of the current chart below.

[v-shex-msft]

Hi @boclifton,

You can refer to below sample to create the visual on power bi desktop.

Table:

Measure:

Date Range(Day) = COUNTROWS(ALL(Sheet1)) //deadline

Actural Line =
var total= MAXX(ALL(Sheet1),[Item Remain])
var unit= total/[Date Range(Day)]
var DateRank=FILTER(ALL(Sheet1),Sheet1[Date]<MAX(Sheet1[Date]))
return
if(total- COUNTX(DateRank,[Date])*unit>0,total- COUNTX(DateRank,[Date])*unit,0)

Create visual:

You can also try to use trend line.

Regards,

Xiaoxin Sheng

[MPNZ]

Hi there

When I try your initial solution, my DateRank measure is not passing and it is providing me this error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value." . I have only referenced one date column, and have tried other columns without success...can you please tell me why else this error may occur?

Thanks

Megan

[boclifton]

This is great, @v-shex-msft!  Thank you!  However, I don't have it working quite yet because I need a little clarification ...

The sample table you reference (Sheet1) is a list of numbers I don't have available.  I've included two samples from my Transactions data.  Since my Transactions table contains data from several projects, can you guide me in getting the Item Remaining column?  Is there a way I can make a calculated table to get this data?  The total items for the project is held in a separate table called Projects, so I'm not sure how to make that work.

[v-shex-msft · Accepted solution]

Hi @boclifton,

You can try to use below formulas to get the remain item and bytes.

Measures:

Remain Items =
var currProject=LASTNONBLANK(Table[ProjectID],[ProjectID])
var TotalItem=SUMX(FILTER(ALL(Table),Table[ProjectID]=currProject),[TransactionItemsMigrated]) + MAX(Table[CurrentItemFailed]) //total items + failed items
return
TotalItem-SUMX(FILTER(ALL(Table),Table[ProjectID]=currProject&&Table[Date]<=MAX(Table[Date])),[TransactionItemsMigrated])

Remain Bytes =

var currProject=LASTNONBLANK(Table[ProjectID],[ProjectID])
var TotalBytes=SUMX(FILTER(ALL(Table),Table[ProjectID]=currProject),[TransactionSizeBytes])
return
TotalBytes-SUMX(FILTER(ALL(Table),Table[ProjectID]=currProject&&Table[Date]<=MAX(Table[Date])),[TransactionSizeBytes])

Regards,

Xiaoxin Sheng