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...
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 )

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.