Forum Discussion

millerkev22's avatar
millerkev22
Regular Visitor
5 years ago
Solved

Develop Table for Burn Down

I have a Table defined as Sheet1 that comes from outside Excel Document. It includes a region slicer, location, Planned Date, and Completed Date.

How can I create a table in Power BI to return the following?

Dates in this Table (defined as Table 2) range from 3/1/2020 to 6/12/2022. Active Date Relationship Many to One for Planned Date in Sheet1 to Date in Table2. Non-active Date Relationship Many to One for Completed Date in Sheet1 to Date in Table2. Planned locations will trend from 291 down to zero based on our plan (aka Sheet 1 Planned Date). Remaining Locations will eventually trend down from 291 down to zero based on our location completed assignements (AKA Sheet 1 Completed Date).

 

Ultimately I want to use this Table to create a Burn Down Chart that shows our performance in completing the location assignments compared to our plan to see if we are trending ahead or behind plan. I also want to have the ability to filter by Region and was hoping I could utilize the Date relationships to accomplish this.

 

Anticipated final result:

 

 

Need Help
  • millerkev22's avatar
    millerkev22
    5 years ago

    FYI, I was able to figure it out. Here is my solution.

     

    Have two tables:

    • Data table that comes from outside source and includes the following:
      • List of Locations
      • Planned Date – Note this is our planned dates
      • Completed Date – Note this is our completed dates
    • Date Table, named DimData, created within Power BI
      • DimDate = Calendar(DATE(2020,3,1),DATE(2022,6,12))

     

    Managed Relationships:

    • Set Data[Planned Date] and DimData[Date] relationship
    • Set Data[Completed Date] and DimData[Date] relationship
    • Set both relationships as inactive

     

    Measures:

     

    CountPlanned =

        CALCULATE (

            COUNT([Planned Date]),

            USERELATIONSHIP ( Data[Planned Date], 'DimDate'[Date] )

        )

    Note: Counts Number of Planned Completions per date and sets Planned Date to DimDate[Date] as the active relationship

     

    CountCompleted =

        CALCULATE (

            COUNTROWS ( Data ),

            USERELATIONSHIP ( Data[Completed Date], 'DimDate'[Date] ),

            Data[Completed Date] <> BLANK ()

           

        )

    Note: Counts Number of Completed Locations per date, filters out the blanks, and sets Completed Date to DimDate[Date] as the active relationship

     

    Scheduled Plan =

    VAR CumulativeTotal =

        CALCULATE (

            [CountPlanned],

            FILTER ( ALLSELECTED ( DimDate ), DimDate[Date] >= MIN ( DimDate[Date] ) )

        )

    RETURN

        IF(CumulativeTotal>0,CumulativeTotal,0)

    Note: If Statement - takes the chart to zero instead of quantity shown for the last date filled planned

     

    Remaining =

    VAR Last =

    LASTNONBLANK ( Data[Completed Date], [Completed Date] )

        /* Defines the last completed date*/

    VAR BeginningStart =

        COUNT ( Data[Location] )

        /* Defines starting value (returns column = count of locations) */

    VAR CumulativeCompleted =

        CALCULATE (

            [CountCompleted],

            FILTER ( ALLSELECTED ( DimDate ), DimDate[Date] <= MAX ( DimDate[Date] ) )

        )

    /* Cumulatively adds up as locations are completed (1,2, 2, 2, 3, 4 etc.) */

    VAR DateColumn =

        SELECTEDVALUE(DimDate[Date])

        /*Sets the column to a variable so that we can reference it to another variable */

    RETURN

        IF (

            DateColumn <= Last,

            BeginningStart - CumulativeCompleted,

            BLANK ()

        )

     

    Linear Burn Rate =

    VAR SprintStartDate =

        CALCULATE ( FIRSTDATE ( DimDate[Date] ), ALLSELECTED ( DimDate ) )

    VAR DaysSinceStart =

        DATEDIFF ( SprintStartDate, MAX ( 'DimDate'[Date] ), DAY )

    VAR BeginningStart =

        COUNT ( Data[Location] )

    VAR SprintLength = 834

    RETURN

        BeginningStart - DaysSinceStart * ( BeginningStart / SprintLength )

3 Replies

  • millerkev22's avatar
    millerkev22
    Regular Visitor
    5 years ago

    FYI, I was able to figure it out. Here is my solution.

     

    Have two tables:

    • Data table that comes from outside source and includes the following:
      • List of Locations
      • Planned Date – Note this is our planned dates
      • Completed Date – Note this is our completed dates
    • Date Table, named DimData, created within Power BI
      • DimDate = Calendar(DATE(2020,3,1),DATE(2022,6,12))

     

    Managed Relationships:

    • Set Data[Planned Date] and DimData[Date] relationship
    • Set Data[Completed Date] and DimData[Date] relationship
    • Set both relationships as inactive

     

    Measures:

     

    CountPlanned =

        CALCULATE (

            COUNT([Planned Date]),

            USERELATIONSHIP ( Data[Planned Date], 'DimDate'[Date] )

        )

    Note: Counts Number of Planned Completions per date and sets Planned Date to DimDate[Date] as the active relationship

     

    CountCompleted =

        CALCULATE (

            COUNTROWS ( Data ),

            USERELATIONSHIP ( Data[Completed Date], 'DimDate'[Date] ),

            Data[Completed Date] <> BLANK ()

           

        )

    Note: Counts Number of Completed Locations per date, filters out the blanks, and sets Completed Date to DimDate[Date] as the active relationship

     

    Scheduled Plan =

    VAR CumulativeTotal =

        CALCULATE (

            [CountPlanned],

            FILTER ( ALLSELECTED ( DimDate ), DimDate[Date] >= MIN ( DimDate[Date] ) )

        )

    RETURN

        IF(CumulativeTotal>0,CumulativeTotal,0)

    Note: If Statement - takes the chart to zero instead of quantity shown for the last date filled planned

     

    Remaining =

    VAR Last =

    LASTNONBLANK ( Data[Completed Date], [Completed Date] )

        /* Defines the last completed date*/

    VAR BeginningStart =

        COUNT ( Data[Location] )

        /* Defines starting value (returns column = count of locations) */

    VAR CumulativeCompleted =

        CALCULATE (

            [CountCompleted],

            FILTER ( ALLSELECTED ( DimDate ), DimDate[Date] <= MAX ( DimDate[Date] ) )

        )

    /* Cumulatively adds up as locations are completed (1,2, 2, 2, 3, 4 etc.) */

    VAR DateColumn =

        SELECTEDVALUE(DimDate[Date])

        /*Sets the column to a variable so that we can reference it to another variable */

    RETURN

        IF (

            DateColumn <= Last,

            BeginningStart - CumulativeCompleted,

            BLANK ()

        )

     

    Linear Burn Rate =

    VAR SprintStartDate =

        CALCULATE ( FIRSTDATE ( DimDate[Date] ), ALLSELECTED ( DimDate ) )

    VAR DaysSinceStart =

        DATEDIFF ( SprintStartDate, MAX ( 'DimDate'[Date] ), DAY )

    VAR BeginningStart =

        COUNT ( Data[Location] )

    VAR SprintLength = 834

    RETURN

        BeginningStart - DaysSinceStart * ( BeginningStart / SprintLength )

    • millerkev22's avatar
      millerkev22
      Regular Visitor
      5 years ago

      Thanks for the response. This helped get me in the direction, unfortunately I still have not been able to resolve the burn down chart.  See below for my progress, and where I still need help:

       

      Here is what has been accomplished and works as planned:

      I followed the link you provided to establish my Date table (referenced below as dimDate) and my active and non active relationships with the Planned and Completed Dates within Sheet 1.

       

      I used the following to establish my burn down chart data for the Planned data:

      CountPlanned = COUNT([Planned Date])

       

      Cumulative Planned =
      VAR CumulativeTotal =
          CALCULATE (
              [CountPlanned],
              FILTER ( ALLSELECTED ( DimDate), DimDate[Date] >= MIN ( DimDate[Date] ) )
          )
      RETURN
          CumulativeTotal

       

      Visualization:

       

       

       

       

       

       

      Here is what I can't figure out and still need assistance with:

      I want to produce data that starts at 291 and trends down based on the Completed Date Column. I also need the data to stop at the last date entry so that it doesn't graph a straight line.  Below are a few measures that I have been toying with but can't seem to figure it out.

       

      Completed = 
      VAR CumulativeTotal2 =
          CALCULATE (
              COUNTROWS ( Sheet 1 ),
              USERELATIONSHIP ( Sheet 1[Completed Date)], 'DimDate'[Date] ),
              Sheet1[Completed Date] <> BLANK ()
          )
      RETURN
          CumulativeTotal2

       

      Cumulative Completed =
      VAR CumulativeTotal3 =
          CALCULATE (
              [Completed],
              FILTER ( ALLSELECTED ( DimDate ), DimDate[Date] >= MIN ( DimDate[Date] ) )
          )
      RETURN
          CumulativeTotal3

       

      Here are the results that I'm seeing:

       

       

       

       

       

       

       

       

       

       

       

      I also attempted to reverse the cumulative Competed function by using <=MAX instead of >= MIN, then I subtracted the Cumulative Plan - Cumulative Completed function. This starts my values at 291, however doesn't stop at the last Completed Date. Below is the functions and results that I'm seeing for this.

      Cumulative Completed2 =
      VAR CumulativeTotal =
          CALCULATE (
              [CountPlanned],
              FILTER ( ALLSELECTED ( DimDate ), DimDate[Date] >= MIN ( DimDate[Date] ) )
          )
      VAR CumulativeTotal2 =
          CALCULATE (
              [Completed],
              FILTER ( ALLSELECTED ( DimDate ), DimDate[Date] <= MAX ( DimDate[Date] ) )
          )
      RETURN
          CumulativeTotal - CumulativeTotal2

       

      Issue with my CompletedData2 is that it's not stopping after the last completed date. In addition, since it is taking the Planned Date - the Completed Dates, I will also have a continuous line throughout the dates.... Any help here is appreciated.