Burn Down Chart Cuts Short

[Juliecal73]

I don't know what I am missing....

I'm trying to create a burn down chart for Projects. I have a Project Details table, Time Listing table and a Calendar table.

The Project table has the Start and End Dates for the Project as well a the Planned Hours, and the Time Listing has the actual captured hours per day (not every day has a value) so it is easy for me to get the burn down Planned and Actual values through measures.

What I can't seem to get is for the Graph to go past the last date that time was captured in the Time Listing table to the Project End Date. Both the actual and planned burn down end on the last day hours were captured for the project.

I followed this video exactly (even though I had some of the field values already, to make sure), and got right to the end where the IF statement was returned, but alas, my graph ends too soon as per below (need the planned to go out to Aug 21)

#Remaining Total Hrs Project = 
VAR Actual = CALCULATE(
    SUM('USA TimeListing'[Hours]),
    FILTER(
        ALLSELECTED('USA TimeListing'), 'USA TimeListing'[TimeDate] <= MAX('USA TimeListing'[TimeDate])
    )
)
VAR RemainActualHrs = CALCULATE(MIN('USA ProjectDetails'[PlannedHours]) - Actual)
RETURN
RemainActualHrs

##Planned Hours = 
VAR ProjectStartDate = CALCULATE(FIRSTDATE('USA TimeListing'[TimeDate]),ALLSELECTED('USA TimeListing'))
VAR DaysSinceStart = DATEDIFF(ProjectStartDate,MAX('@Calendar'[Date]),DAY)
VAR BeginningStart = MAX('USA ProjectDetails'[PlannedHours])
VAR ProjectLength = DATEDIFF(ProjectStartDate,MAX('USA ProjectDetails'[Project Planned End]),DAY)
RETURN
IF(
    MAX('@Calendar'[Date]) >= ProjectStartDate && MAX('@Calendar'[Date]) <= ProjectStartDate + ProjectLength,
    BeginningStart - DaysSinceStart * (BeginningStart/ProjectLength),
    BLANK()
)

 Thoughts?

EDIT: I notice that if I cut the planned hours date shorter than the actual, then it 'works' (not desired result), so is this to do wiht the fact that my 'Remaining Hours' is a measure and not a column in the table (like the video)?

[Juliecal73 · Accepted solution]

I managed to solve this. It seems it I needed to calculate my values referencing the calendar data

VAR PlannedHours = CALCULATE(SUM('USA ProjectDetails'[PlannedHours]), DATESYTD('@Calendar'[Date]))

My full measure looks like this

#Burndown Planned = 
VAR ProjectStart = CALCULATE(MIN('USA ProjectDetails'[Project Planned Start]), DATESYTD('@Calendar'[Date]))
VAR ProjectEnd = CALCULATE(MAX('USA ProjectDetails'[Project Planned End]), DATESYTD('@Calendar'[Date]))
VAR PlannedHours = CALCULATE(SUM('USA ProjectDetails'[PlannedHours]), DATESYTD('@Calendar'[Date]))
VAR DaysInProject = CALCULATE(DATEDIFF(ProjectStart,ProjectEnd,DAY))
VAR PlannedBurn = CALCULATE(PlannedHours / DaysInProject)
VAR DaysSinceStart = CALCULATE(DATEDIFF(ProjectStart,MAX('@Calendar'[Date]),DAY))
VAR CurrentPlannedBurn = PlannedBurn * DaysSinceStart
RETURN
IF(
    MAX('@Calendar'[Date]) >= ProjectStart && MAX('@Calendar'[Date]) <= ProjectEnd,
    PlannedHours - (DaysSinceStart * (PlannedHours/DaysInProject)),
    BLANK()
)

[v-lili6-msft]

hi  @Juliecal73 

For your case, i think you may have a this blog:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

Regards,

Lin