Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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
)
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |