Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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:

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


Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    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.

11 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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

      • v-jingzhang's avatar
        v-jingzhang
        Community Support
        5 years ago

        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.