Burndown Chart - Dynamically Created Table Part 2

[titanconsulting]

Yesterday I was helped by @amitchandak  and @Greg_Deckler we both kind enough to help me out on creating a dynamic table.  Thought I was in a good space, but realize I still have a gap.  I stripped down my pbix to what's needed here to see if anyone has some thoughts.

Creating a dynamic table worked - tying everything together did not.

1. My gut tells me that I've set up the dynamic table to create a set of date records corresponding to the days of the specified sprint.  Sprints are filtering, Projects are not.  Sprint 1 of a different project gets calculated into the table.
2. I'm also struggling to get the proper calculation on ideal points total.  I have the correct number of ideal points per day (23.41).  Doing poorly on dropping that amount from the total each day.  Day 1 should be 515, Day 2 should be 515 - 23.41, etc.  Again - does this tie back to not connecting the Project to the table correct?

[Greg_Deckler]

I think that there are a number of things that you could do to improve the model. However, I have taken what you have and added a disconnected Calendar table that makes things at least somewhat easier. And I have created an Idealized Burndown measure that demonstrates what needs to happen. PBIX is attached.

Idealized Burndown = 
    VAR __ReportingDate = DATE(2020,4,1)
    VAR __Date = MAX('R07_Calendar'[Date])
    VAR __StartDate = MINX('R07_Project','R07_Project'[Start_Date])
    VAR __FinishDate = MAXX('R07_Project','R07_Project'[Finish_Date])
    VAR __TotalProjectHours = SUMX(ALL('R07_Project'),'R07_Project'[Scheduled_Work])
    VAR __IdealHoursPerDay = 
        DIVIDE(
            __TotalProjectHours,
            DATEDIFF(__StartDate,__FinishDate,DAY) + 1,
            0
        )
    VAR __IdealConsumedHours = 
            __IdealHoursPerDay * (DATEDIFF(__StartDate,__Date,DAY) + 1)
RETURN
    IF(
        __Date < __StartDate - 1 || __Date > __FinishDate,
        BLANK(),
        __TotalProjectHours - __IdealConsumedHours
    )

[titanconsulting]

Greg,

Been poring over this the last few days to figure it out.  I think I understand your comment on the organization - putting values in measures instead of additional columns for tables.

However, your output gives me a burndown chart with calendarauto.  The burndown is only viable from Sprints[StartDt] until Sprints[EndDt].  That's the only dates that should show on the burndown report and the only dates that are used for the calculations of points.  I've tried using the calendar function filtered for the selected Project and Sprint's start and end date.  

Now that you've shown me the calculations - I understand and can figure that out.  I'll keep calculations outside of the calendar table.  But I can't keep the dates down to just the selected values of the Project/Sprint I have on the screen.

SprintDays =

  VAR _PROJECT = SELECTEDVALUE(Sprints[Project])

  VAR _SPRINT = SELECTEDVALUE(Sprints[Sprint])

  VAR _STARTDT = MINX(FILTER(Sprints, Sprints[Project] = _PROJECT && Sprints[Sprint] = _SPRINT), Sprints[StartDt])

  VAR _ENDDT = MAXX(FILTER(Sprints,Sprints[Project] = _PROJECT && Sprints[Sprint] = _SPRINT), Sprints[EndDt].[Date])

RETURN

  calendar( _STARTDT, _ENDDT)

This is currently not producing any dates.  Just experimenting - I can create the Selected Project and Selected Sprint outside of this DAX and it will give me the correct values.  Start and End date can also be correctly calculated outside of this DAX and I can show the correct dates on the report.  I just can't use them correctly in calculation of the calendar table.

[titanconsulting · Accepted solution]

May be hacky, but I figured it out.  Created a second table for straight calendar dates to determine weekends.  All measures were then created to be used with the first calendar.  When measure calculations were used against first calendar, blanks did not show on the visual - so only my Sprint dates showed.