locked
Burn down chart has weird spikes RRS feed

  • Question

  • Creating a burn down chart using a running total of cumulative hours with the following formula:

    CumulativeHoursLeft:=CALCULATE (
        SUM('Projects'[Budget hours]) - SUM ( 'hours'[Hours] ),
        FILTER (
            ALL ( 'hours'[Date] ),
            'hours'[Date] <= MAX ('hours'[Date])
        )
    )

    Works great except that in a Line Chart using [Date] as the Axis and CumulativeHoursLeft as the value, I get these spikes on days for which the employee reported no hours. I do know what exactly the measure is doing in this instance and I do not get this in a table, those dates simply do not appear. I have tried both Categories and Continuous for the Line Chart. I have also tried filtering where [Date] is not blank. No luck.

    Does anyone have any suggestions on how to get rid of the spikes?

    Tuesday, July 21, 2015 2:25 PM

Answers

  • Well, I think I found it although it leaves blanks in my burn down chart but at least it is readable versus the spikes.

    CumulativeHoursLeft:=IF(ISBLANK(COUNT('data'[PA Date])),-999,CALCULATE (

        SUM('Projects'[Budget hours]) - SUM ( 'data'[PA Quantity] ),

        FILTER (

            ALL ( 'data'[PA Date] ),

            'data'[PA Date] <= MAX ('data'[PA Date])

        )

    ))

    Then I filter out -999 for my chart.

    Hacky. :(

    Thursday, July 23, 2015 2:39 AM

All replies

  • Excel 2010 with free PowerPivot Add-In.
    Compatible with Office 2013 Pro Plus.
    Un-spiked your un-known database.
    http://www.mediafire.com/view/2hd3o0t4o1gt70f/07_21_15a.xlsx

    Wednesday, July 22, 2015 3:20 AM
  • First, I really appreciate you taking a look at this issue.

    I'm going to fiddle with this and see if I can adapt the approach to my purposes, however that setting doesn't seem to work with measures from what I can see. Let me provide a sample of my data:

    Hours

    • EmployeeID, ProjectID, Date, Hours
    • MOUPRES, PROJ1, 7/20/2015, 2
    • MOUPRES, PROJ1, 7/22/2015, 4.5

    Projects

    • ProjectID, EmployeeID, BudgetHours
    • PROJ1, MOUPRES, 24

    That's the basic raw data format.  I import these as well as create an Employee table with unique EmployeID's, same for ProjectID and same for Dates.  The issue with your approach that I am still working through is that I have over 200 consultants (EmployeeID's) and as many or more Projects. That was why using a measure directly in the pivot chart/power view is so appealing because it automatically takes the context into account in terms of the employee, project filter and date, except for the spikes which I still don't get at some level, god only knows what the measure is doing in those circumstances. What it appears to be doing is spiking back up to the original budgeted hours amount, so the line chart values for the above data would be:

    • 7/20/2014, 22
    • 7/21/2014, 24
    • 7/21/2014, 17.5

    I hope that makes sense.

    I either have to fill in the gaps for those dates in the data (Hours), somehow with zero hours for any projects not present for projects assigned to an employee (that would be a joyful Power Query query) or I have to somehow make the measure recognize that if it has no data for a particular date to go with the previous value or something. Or, I need to make the line chart not "fill in" date values, which might be hard because multiple people bill on the same project but not all always on the same days.

    Aha moment! THAT's ultimately the issue. If I add a slicer for EmployeeID and slice by Project and Employee, I get a nice, smooth burn down chart because dates for which there is no data arent' getting interjected into the mix. Hmmm, now I have to think about that.

    Wednesday, July 22, 2015 1:07 PM
  • Hmm, I'm probably doing this wrong I don't get so much of a burn down using this:

    CumulativeHoursLeft1:=CALCULATE (SUM(Projects[Budget hours]) - SUM ( 'hours'[Hours] ),

       FILTER ('hours',

             'hours'[Date]

                 = CALCULATE(

                     LASTNONBLANK ( 'hours'[Date], 1 ), FILTER (

            ALL ( 'hours'[Date] ),

            'hours'[Date] <= MAX ('hours'[Date])

    ))

        )

    )

    Wednesday, July 22, 2015 9:48 PM
  • Well, I think I found it although it leaves blanks in my burn down chart but at least it is readable versus the spikes.

    CumulativeHoursLeft:=IF(ISBLANK(COUNT('data'[PA Date])),-999,CALCULATE (

        SUM('Projects'[Budget hours]) - SUM ( 'data'[PA Quantity] ),

        FILTER (

            ALL ( 'data'[PA Date] ),

            'data'[PA Date] <= MAX ('data'[PA Date])

        )

    ))

    Then I filter out -999 for my chart.

    Hacky. :(

    Thursday, July 23, 2015 2:39 AM