help with a simple burndown chart

[AndreSatziack]

Hello Team,

I need to create a burndown line chart with sample data below.
I tried use the follow dax code but don't worked for me, can you help me?

Thank you very much.

BurnDown = 180 - CALCULATE(SUM(Relatorio_Completo_2017[Hours]), FILTER(ALLSELECTED(Relatorio_Completo_2017[Criado]),ISONORAFTER(Relatorio_Completo_2017[Criado],MAX(Relatorio_Completo_2017[Criado]),DESC))) 

[Ashish_Mathur · Accepted solution]

Hi @AndreSatziack,

You may download my solution from here.

Hope this helps.

[v-yuezhe-msft]

@AndreSatziack,

Create your measure using DAX below.

BurnDown = 180-CALCULATE(SUM(Relatorio_Completo_2017[Hours]),FILTER(ALL(Relatorio_Completo_2017),Relatorio_Completo_2017[Criado]<=MAX(Relatorio_Completo_2017[Criado])))

If the above Dax doesn't help, please help to share sample data of your table and post expected result here.

Regards,
Lydia

[AndreSatziack]

Hello @v-yuezhe-msft,

Thank you very much for your help 🙂

This DAX helps but I can't filter by customer organization, it only shows the total amount of all customers.

Another questions is there a trend line to predict when the available hours will be depleted.

See bellow and Download my files Files Sample

Thank you very much

[AndreSatziack]

Hello @v-yuezhe-msft,

How are you? Did you get a look at my example?

Thank you very much 🙂

Best Regards.

[v-yuezhe-msft]

@AndreSatziack,

What is your expected result when you set the value to customer1 in visual level filters? And what type of trend line would you like to add?

Regards,

Lydia

[AndreSatziack]

Hello @v-yuezhe-msft,

Thank you for your help.

I resolved the filter problem removing the parameter ALL from FILTER Like "BurnDown = 180-CALCULATE(SUM(Relatorio_Anual_2017[Hours]),FILTER((Relatorio_Anual_2017),Relatorio_Anual_2017[Created]<=MAX(Relatorio_Anual_2017[Created])))"

Your DAX code show me results for each month with subtraction of 180.

I need to show the estimated date of finish hours based on an average of hours used in month like my sample in Excel. 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.

[Ashish_Mathur]

Hi @AndreSatziack,

What result are you expecting when you say "I need to show the expected date of finish........"?

[AndreSatziack]

Hello @Ashish_Mathur,

The idea is that a client has a total of 180 hours per year that they can use. I have to calculate this burndown, using the logic that the client starts the year with 180 hours in january, but in february i have to use the resultant burndown from the hours consumed in january instead of 180. I am having difficulties on coding this logic. With that then I will need to show a graph with a trend line.

Thank you very much.

[Ashish_Mathur]

Hi @AndreSatziack,

But that is something you have already computed in your PBIX file.  You already have that solution.

[AndreSatziack]

Hello @Ashish_Mathur,

My code "BurnDown = 180 - CALCULATE(SUM(Relatorio_Completo_2017[Hours]), FILTER(ALLSELECTED(Relatorio_Completo_2017[Criado]),ISONORAFTER(Relatorio_Completo_2017[Criado],MAX(Relatorio_Completo_2017[Criado]),DESC)))" only works for January and not for the rest of the months.

I need to use the last month result instead of 180 in the next months except January because the start with 180 hours to burn throughout the year.

This is a sample in Excel. I need to do this in Power Bi but I don't know how. Thank you very much 🙂

[Ashish_Mathur · Accepted solution]

Hi @AndreSatziack,

You may download my solution from here.

Hope this helps.

[AndreSatziack]

Hello @Ashish_Mathur,

Thank you very much. I'm very grateful for your help. This solved my problem 🙂 🙂

Can you help me one more last time? I need to filter the chart by created date, some customers starts the burndown in different dates. The chart always starts in January. I need it to start by created date filter.

See the current status.

Thank you very much.

[Ashish_Mathur]

You are most welcome @AndreSatziack.  Share the link from where i can download the file where you have that case.

[AndreSatziack]

Hello @Ashish_Mathur and @v-yuezhe-msft, Thank you very much for your help 🙂

I'm very grateful for your help. You are the best! 🙂

Best Regards Team!

[Ashish_Mathur]

You are welcome.

[Anonymous]

None of these work for me. I am pulling history in VSTS. This is summation of Remaining work by Changed Date and Is Current flag.

Please help me with this burndown chart.

[Anonymous]

Non of these work for me. I am pulling history in VSTS. This is summation of Remaining work by Changed Date and Is Current flag.

Please help me with this burndown chart.

[v-yuezhe-msft · Accepted solution]

@AndreSatziack,

Create the following measures in your table and check if you get expected result.

Burndown = IF([Total hours]=0 ||[Total hours]=BLANK(),BLANK(), 3200 - CALCULATE(SUM(Relatorio_Anual_2017[Hours]),FILTER(ALL(Relatorio_Anual_2017),Relatorio_Anual_2017[Created]<=MAX(Relatorio_Anual_2017[Created]))))

Burndown1 = IF([Total hours]=0 ||[Total hours]=BLANK(),BLANK(),3200-CALCULATE([Total hours],DATESYTD('Calendar'[Date],"31/12")))

Regards,
Lydia

[AndreSatziack]

Hello Lydia @v-yuezhe-msft, Thank you very much again, your dax code bellow resolved my created date filter.

Burndown1 = IF([Total hours]=0 ||[Total hours]=BLANK(),BLANK(),3200-CALCULATE([Total hours],DATESYTD('Calendar'[Date],"31/12")))

Best Regards.