Forum Discussion

mathias1998's avatar
mathias1998
Frequent Visitor
3 years ago

Burn down chart based on filled out rows

Hi, 

 

I have the following columns: 

- Date 

- ID 

- Approval Planned

- Approval Actual

 

 

As shown above, not all rows contain data.

 

What I would like, is to create a burn down chart that shows the planned vs actual number of approved IDs, like below. An ID is approved once there is a date inserted in the "Approved Actual" column. 

 

It would be great if the start date in the visualization would be the same date as the first date appearing in the Approval Planned column. On that start date, the remaining approvals should equal the number of IDs found in the ID column. 

 

 

I am fairly new to PowerBI, and have searched on Google for several dates without finding an appropiate solution ðŸ¤”

Need Help

2 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi mathias1998 

     

    To make the start date be the same date as the first date appearing in the Approval Planned column, you can add a Date table to your model. Create a Date table with below formula. Then use this Date table's Date column on x-axis of the line chart. 

    Date = CALENDAR ( MIN ( 'Table'[Approval Planned] ), DATE ( 2022, 12, 31 ) )

     

    But I'm not sure what should be the y-axis value on the chart. Do you want to count the number of IDs on every date and populate the count results on y-axis? 

     

    Best Regards,
    Community Support Team _ Jing

    • mathias1998's avatar
      mathias1998
      Frequent Visitor
      3 years ago

      Hi v-jingzhang,

       

      Thank you for the quick response. I already have a date table, which I used to make a new merged table where the ID, Approval Planned, and Approval Actual columns have been added.

       

      The y-axis values will be the count of ID. There will be two lines on the burn down chart. The first one will be the one for Approval Actual
      = total count of IDs - count of rows that contain a date in the Approval Actual column up until the corresponding date on the x-axis

       

      The second line will be for Approval Planned

      = total count of IDs - count of rows that contain a date in the Approval Planned column up until the corresponding date on the x-axis

       

      As an example:

      Total count of IDs = 15 (i.e. max value on the Y-axis). 

      Date on the x-axis = 22.10.2022.

      Approval Planned line's Y-axis value = 14, because the Approval Planned column does contain 1 date which is either 22.10.2022 or earlier (i.e. not null).

      Approval Actual line's Y-axis value = 15, because the Approval Actual column does not contain any date which is either 22.10.2022 or earlier (i.e. is null).