Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Burndown Graph with deadlines

Hi all,

 

I'm trying to create a graph that shows the expected burndown of milestones of a program. I would like to compare the planned dates with the actual dates. My goal would be two lines that start 25 and decline to 0 over time.
I have a table with numerous names for milestones, where a milestone name can appear multiple times for different projects [Milestone Name] , and a date on which the milestons will be completed [End date approved]. A sample of the data that i would like to use looks like this:

 

Planned date Actual dateMilestone Name
31/12/202029/12/2020Example 1
31/12/202031/12/2020Example 2
30/04/202117/03/2020Example 3
30/06/202105/07/2020Example 4
30/09/202130/09/2021Example 5
30/10/2021 25/9/2021Example 6
30/10/202125/9/2021Example 7
30/10/202125/9/2021Example 8
30/10/202130/9/2021Example 9
30/10/202130/9/2021Example 10
30/10/202130/9/2021Example 11
30/10/202130/9/2021Example 12
30/10/202115/10/2021Example 13
30/10/202115/10/2021Example 14
30/10/202115/10/2021Example 15
30/10/202115/10/2021Example 16
30/10/202115/10/2021Example 17
30/10/202115/10/2021Example 18
30/10/202110/11/2021Example 19
30/10/202110/11/2021Example 20
30/10/202110/11/2021Example 21
30/10/2021 25/10/2021Example 22
31/12/202131/12/2021Example 23
31/12/202131/12/2021Example 24
31/03/202228/01/2022Example 25

 

The graph would ideally starts at the first date, with the total amount of milestones to go, and should end at 0 on the last date (31-03-2022). Something like this:

Odi_1-1620283508139.png

(applogies for the paint drawing)

 
I have read numerous posts on burndown charts in the community but I still can't seem to get this working. I'm fairly new to powerbi and any help is much appreciated !

 

Thank you!


1 ACCEPTED SOLUTION

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.

View solution in original post

11 REPLIES 11
amitchandak
Super User
Super User

@Anonymous , with help from date table like

 

Cumm Sales = CALCULATE(count(Table[Milestone]),filter(allselected(Date),Date[Date] >=max(Date[Date])))

or

Cumm Sales = CALCULATE(count(Table[Milestone]),filter(allselected(Table),Table[Planned Date] >=max(Table[Planned  Date])))

Anonymous
Not applicable

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?

Odi_0-1620293268829.png

 

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.

Anonymous
Not applicable

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, 
Odi

Hi @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 _ Jing

Anonymous
Not applicable

Hi @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.

Odi_1-1621408334990.png


Kind regards,
Odi

 

Hi @Anonymous 

 

Only one active relationship is allowed to exist between two tables so you met this problem. We have a common solution to deal with it by using USERELATIONSHIP() function to activate an inactive relationship in this measure.

 

Suppose you have an active relationship on Planned Date column, you can use previous measure to get the result. At the same time, you can create an inactive relationship between Actual Date column and 'Date'[Date] column. Then you can use below measure to calculate the value based on actual date. 

Actual to do =
CALCULATE (
    COUNT ( 'Table'[Milestone Name] ),
    FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Date] > MAX ( 'Date'[Date] ) ),
    USERELATIONSHIP ( 'Date'[Date], 'Table'[Actual Date] )
) + 0

 

Reference: Active And Inactive Data Relationships In Power BI

 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

Anonymous
Not applicable

Hi @v-jingzhang,

Thanks a lot! I get the following visual now:

Odi_0-1621427361431.png


It does everything I've asked of you. If I may be so bold as to ask one more question:
Is it possible to tweek the visual or data such that the graph doesn't show every single date, but only the dates that with data points in them? Making the lines look more fluid:

Odi_1-1621427487170.png 
instead of 

Odi_2-1621427650824.png

If there is a solution for that I would be very gratefull. We're using these graphs throughout our organisation.

Kind regards,
Luca

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.

Anonymous
Not applicable

Hi @v-jingzhang ,

Beautiful!

Odi_0-1621503010192.png

Thank you so much for your help!

Kind regards,
Odi

Hi @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] ) )
) + 0

 

Regards,
Jing

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.