Forum Discussion
Burndown Graph with deadlines
Hi Anonymous
Try below measures.
Planned to do = VAR _count = CALCULATE ( COUNT ( 'Table'[Milestone Name] ), FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Date] > MAX ( 'Date'[Date] ) ) ) + 0 RETURN IF ( ISBLANK ( SELECTEDVALUE ( 'Table'[Planned date] ) ), BLANK (), _count )Actual to do = VAR _count = CALCULATE ( COUNT ( 'Table'[Milestone Name] ), FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Date] > MAX ( 'Date'[Date] ) ), USERELATIONSHIP ( 'Date'[Date], 'Table'[Actual Date] ) ) + 0 VAR _date = CALCULATE ( SELECTEDVALUE ( 'Table'[Actual date] ), USERELATIONSHIP ( 'Date'[Date], 'Table'[Actual Date] ) ) RETURN IF ( ISBLANK ( _date ), BLANK (), _count )Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
Hi Amitchandak,
Thank you for the quick reply. I tried your solution, but it doesn't end at 0. It appears that the entire graph should shift one date entry, if that makese sense. Do you know if the measure can be adjusted so that it does?
Kind regards,
Odi
Hi Anonymous
It seems ending at 1 is correct because on the last date (31-03-2022) there is the last one Milestone Example 25. If you want it to show the total amount of milestones to go (not including current day), you could replace >= with > in Amit's formula. But in this way, it will not start at 25 as the milestones on the first day will be filtered out.
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
Hi v-jingzhang ,
Thank you for your answer!
I understand that the measure ends at 1, since the last date has one value. I think the measure works the way it should, unforunately though, it doesn't achieve what i'm trying to achieve. I tried changing >= to >, but it doesn't reach my goal.
I would like to get a graph that ends at 0, based on the idea that if all milestones have indeed been finished by the last date the "to do" value should be 0 and not 1.
This might not be something that is possible to achieve with a measure, I wouldn't know.
The reason I would like to get to 0 is that in my real data I have 150+ milestones at the last date, which results in a burndown graph that ends at 150+ somewhere in the middle of the graph, which is not the expected end, because you expect to end at 0. Hence the business is not happy with my graph 😉
I hope this is a clear explanation and I'm curious to know if there is a solution for it. A measure would be really nice, but if there is another way that would also be good.
Kind regards,
OdiHi Anonymous
It is because blank values don't show in the chart. You could add +0 at the end of the measure.
Planned to do = CALCULATE ( COUNT ( 'Table'[Milestone Name] ), FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Date] > MAX ( 'Date'[Date] ) ) ) + 0Regards,
JingHi Anonymous
Does my reply solve your problem? If yes, kindly accept it as the solution. Otherwise, please provide more details about your problem so that we can work on it further. Thanks.
Regards,
Community Support Team _ JingHi v-jingzhang ,
Apollogies for not replying before. I've looked at your solution and with the +0 the graph does indeed go to 0, which is very nice!
I did notice that with >= it doesn't reach 0, but with > it does. As you already mentioned it then filters out the first date, which is a pity, but not limiting.
I'm still struggling with one thing, and that is to get both lines (measures) correctly in one graph. I understood from amitchandak that I have to use a date table?
I've created a table with all dates from 2020 to 2023, but if I try to use the date from that table on my x-axis it doesn't work. I can't relate both date columns to the one calendar... Could very well be that i'm not doing the right thing here, so if you could assist on that it would be great.
I've posted a picture below, i hope it's visible. I would add the pbix file but I can't upload it to the message, and I'm not allowed to share via dropbox etc.
Kind regards,
Odi