Develop Table for Burn Down

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 )