Paste your Planner export, click Refresh, and see which project is slipping and who is overloaded. Project Management is a Power BI dashboard for project managers and PMOs who track their work in Excel or Microsoft Planner. Paste your tasks, costs and risks into one Excel workbook, click Refresh, and you get the health of every project, a Gantt timeline, team workload and a risk heat map. It answers the questions a status meeting needs: which projects are slipping, which are heading over budget, who is overloaded in the next four weeks, and what is already overdue. The finished Project Management dashboard Prefer the ready-made template? Get Project Management for $19, with the dashboard, the Excel file and a step-by-step guide: Click to Buy Watch the full build on YouTube: https://www.youtube.com/@PivotalstatsProjects (Click to view Video) What you need Power BI Desktop from Microsoft (Windows), 2025 or newer Microsoft Excel The sample data file Project Tracker.xlsx: Click to Download What you'll build A project portfolio dashboard in Power BI, built from one Excel workbook: the health of every project, a Gantt timeline with milestones, team workload for the next four weeks, and a risk heat map. It uses Power Query, a small star schema, DAX and only Microsoft's standard visuals. The Gantt chart is drawn inside a normal table, and the logo, icons and illustrations are separate objects you can replace. 6 of 9 on track 64% done, plan says 87% Step 01: The three pages Overview: portfolio health (on track, at risk, off track, done), budget spent and the forecast at completion, four KPI cards, a Projects table with progress against plan and budget used, the spend curve against the phased budget, and the next milestones. Timeline: a Gantt chart drawn inside a table, with milestones and a today line; click a project to see its tasks. Team & Risks: workload against capacity for the next four weeks, a 5 x 5 risk heat map, a burn-up chart and the overdue tasks. Portfolio health $2.3M of $3.2M spent Projects table Step 02: One Excel workbook, six tables Projects holds the department, owner, planned start and finish and budget. Tasks is laid out like Microsoft Planner's Export plan to Excel (task name, bucket, progress, priority, assigned to, start and due date, labels), plus two extra columns: Project and Effort (hrs). A task with the label Milestone is a milestone. Costs is the actual spend per project per month. People lists everyone who is assigned tasks, with their capacity in hours per week. Risks scores impact and likelihood from 1 to 5. Setup holds the status date; leave it empty to use today. Every tab is an Excel table, so it grows as you paste rows underneath. Planner layout Two extra columns Milestones Step 03: Connect with one parameter In Power Query, create a text parameter called DataFile that holds the full path to the workbook. Every query reads from it, so moving the file means changing one value. Then add a blank query called Workbook that opens the file with Excel.Workbook. It lists the sheets and the tables; always read the tables, because they grow with your data. Switch off Enable load: it is only the shared source for the other queries. DataFile parameter Read the tables M let Source = Excel.Workbook(File.Contents(DataFile), null, true) in Source Step 04: Clean the Planner tasks TasksRaw selects the twelve columns by name with MissingField.UseNull, so a missing column arrives empty instead of breaking the refresh. It renames them, trims the text, sets the types, removes rows that fail as a date or number, and keeps every task with a name and at least one date. A task with only a due date starts on that day, and the other way round. Blank progress becomes Not started, a blank assignee Unassigned and a blank effort 8 hours. IsMilestone is 1 when Labels contains Milestone, and Credit is 1 for Completed, 0.5 for In progress and 0 for Not started. MissingField.UseNull Filled Dates Is Milestone M let Source = Workbook{[Item = "Tasks", Kind = "Table"]}[Data], #"Selected Columns" = Table.SelectColumns(Source, {"Task ID", "Task Name", "Bucket Name", "Progress", "Priority", "Assigned To", "Start date", "Due date", "Completed Date", "Labels", "Project", "Effort (hrs)"}, MissingField.UseNull), #"Renamed Columns" = Table.RenameColumns(#"Selected Columns", {{"Task ID", "TaskID"}, {"Task Name", "Task"}, {"Bucket Name", "Bucket"}, {"Assigned To", "AssignedTo"}, {"Start date", "StartDate"}, {"Due date", "DueDate"}, {"Completed Date", "CompletedDate"}, {"Effort (hrs)", "Effort"}}), #"Trimmed Text" = Table.TransformColumns(#"Renamed Columns", {{"TaskID", each if _ = null then null else Text.Trim(Text.From(_)), type text}, {"Task", each if _ = null then null else Text.Trim(Text.From(_)), type text}, {"Bucket", each if _ = null then null else Text.Trim(Text.From(_)), type text}, {"Progress", each if _ = null then null else Text.Trim(Text.From(_)), type text}, {"Priority", each if _ = null then null else Text.Trim(Text.From(_)), type text}, {"AssignedTo", each if _ = null then null else Text.Trim(Text.From(_)), type text}, {"Labels", each if _ = null then null else Text.Trim(Text.From(_)), type text}, {"Project", each if _ = null then null else Text.Trim(Text.From(_)), type text}}), #"Changed Type" = Table.TransformColumnTypes(#"Trimmed Text", {{"StartDate", type date}, {"DueDate", type date}, {"CompletedDate", type date}, {"Effort", type number}}, "en-US"), #"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type", {"StartDate", "DueDate", "CompletedDate", "Effort"}), #"Kept Tasks" = Table.SelectRows(#"Removed Errors", each [Task] <> null and [Task] <> "" and ([StartDate] <> null or [DueDate] <> null)), #"Filled Dates" = Table.TransformRows(#"Kept Tasks", each _ & [StartDate = if [StartDate] = null then [DueDate] else [StartDate], DueDate = if [DueDate] = null then [StartDate] else [DueDate]]), #"Back To Table" = Table.FromRecords(#"Filled Dates", Table.ColumnNames(#"Kept Tasks")), #"Default Progress" = Table.ReplaceValue(#"Back To Table", null, "Not started", Replacer.ReplaceValue, {"Progress"}), #"Default Assignee" = Table.ReplaceValue(#"Default Progress", null, "Unassigned", Replacer.ReplaceValue, {"AssignedTo"}), #"Default Project" = Table.ReplaceValue(#"Default Assignee", null, "No project", Replacer.ReplaceValue, {"Project"}), #"Default Effort" = Table.ReplaceValue(#"Default Project", null, 8, Replacer.ReplaceValue, {"Effort"}), #"Added Index" = Table.AddIndexColumn(#"Default Effort", "Row", 1, 1, Int64.Type), #"Default ID" = Table.TransformRows(#"Added Index", each _ & [TaskID = if [TaskID] = null or [TaskID] = "" then "T" & Text.PadStart(Text.From([Row]), 4, "0") else [TaskID]]), #"ID Table" = Table.RemoveColumns(Table.FromRecords(#"Default ID", Table.ColumnNames(#"Added Index")), {"Row"}), #"Added Milestone" = Table.AddColumn(#"ID Table", "IsMilestone", each if [Labels] <> null and Text.Contains([Labels], "Milestone", Comparer.OrdinalIgnoreCase) then 1 else 0, Int64.Type), #"Added Credit" = Table.AddColumn(#"Added Milestone", "Credit", each if [Progress] = "Completed" then 1 else if [Progress] = "In progress" then 0.5 else 0, type number), #"Typed" = Table.TransformColumnTypes(#"Added Credit", {{"TaskID", type text}, {"Task", type text}, {"Bucket", type text}, {"Progress", type text}, {"Priority", type text}, {"AssignedTo", type text}, {"StartDate", type date}, {"DueDate", type date}, {"CompletedDate", type date}, {"Labels", type text}, {"Project", type text}, {"Effort", type number}}) in #"Typed" Step 05: Projects, people, costs and risks Projects adds any project that appears in Tasks but not on the Projects sheet, taking its start and finish from its tasks, so a new plan appears before anyone fills in a budget. People adds any assignee who is missing from the People sheet, with a default capacity of 32 hours a week. Costs moves every date to the first day of its month. Risks clamps Impact and Likelihood to 1-5 and multiplies them into a Score from 1 to 25. Missing projects added Missing people added M let Source = Workbook{[Item = "Projects", Kind = "Table"]}[Data], #"Selected Columns" = Table.SelectColumns(Source, {"Project ID", "Project", "Department", "Owner", "Start Date", "Finish Date", "Budget"}, MissingField.UseNull), #"Renamed Columns" = Table.RenameColumns(#"Selected Columns", {{"Project ID", "ProjectID"}, {"Start Date", "StartDate"}, {"Finish Date", "FinishDate"}}), #"Trimmed Text" = Table.TransformColumns(#"Renamed Columns", {{"ProjectID", each if _ = null then null else Text.Trim(Text.From(_)), type text}, {"Project", each if _ = null then null else Text.Trim(Text.From(_)), type text}, {"Department", each if _ = null then null else Text.Trim(Text.From(_)), type text}, {"Owner", each if _ = null then null else Text.Trim(Text.From(_)), type text}}), #"Changed Type" = Table.TransformColumnTypes(#"Trimmed Text", {{"StartDate", type date}, {"FinishDate", type date}, {"Budget", type number}}, "en-US"), #"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type", {"StartDate", "FinishDate", "Budget"}), #"Known Projects" = Table.Distinct(Table.SelectRows(#"Removed Errors", each [Project] <> null and [Project] <> ""), {"Project"}), #"New From Tasks" = List.Difference(List.Distinct(TasksRaw[Project]), #"Known Projects"[Project]), #"Missing Projects" = Table.FromRecords(List.Transform(#"New From Tasks", (p) => let t = Table.SelectRows(TasksRaw, each [Project] = p) in [ProjectID = null, Project = p, Department = "Other", Owner = null, StartDate = List.Min(t[StartDate]), FinishDate = List.Max(t[DueDate]), Budget = null])), #"All Projects" = if List.IsEmpty(#"New From Tasks") then #"Known Projects" else Table.Combine({#"Known Projects", #"Missing Projects"}), #"Default Department" = Table.ReplaceValue(#"All Projects", null, "Other", Replacer.ReplaceValue, {"Department"}), #"Default Owner" = Table.ReplaceValue(#"Default Department", null, "Unassigned", Replacer.ReplaceValue, {"Owner"}), #"Typed" = Table.TransformColumnTypes(#"Default Owner", {{"ProjectID", type text}, {"Project", type text}, {"Department", type text}, {"Owner", type text}, {"StartDate", type date}, {"FinishDate", type date}, {"Budget", type number}}) in #"Typed" M let Source = Workbook{[Item = "Risks", Kind = "Table"]}[Data], #"Selected Columns" = Table.SelectColumns(Source, {"Risk ID", "Project", "Risk", "Impact", "Likelihood", "Owner", "Status", "Raised"}, MissingField.UseNull), #"Renamed Columns" = Table.RenameColumns(#"Selected Columns", {{"Risk ID", "RiskID"}}), #"Trimmed Text" = Table.TransformColumns(#"Renamed Columns", {{"RiskID", each if _ = null then null else Text.Trim(Text.From(_)), type text}, {"Project", each if _ = null then null else Text.Trim(Text.From(_)), type text}, {"Risk", each if _ = null then null else Text.Trim(Text.From(_)), type text}, {"Owner", each if _ = null then null else Text.Trim(Text.From(_)), type text}, {"Status", each if _ = null then null else Text.Trim(Text.From(_)), type text}}), #"Changed Type" = Table.TransformColumnTypes(#"Trimmed Text", {{"Impact", Int64.Type}, {"Likelihood", Int64.Type}, {"Raised", type date}}, "en-US"), #"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type", {"Impact", "Likelihood", "Raised"}), #"Kept Risks" = Table.SelectRows(#"Removed Errors", each [Risk] <> null and [Risk] <> ""), #"Clamped" = Table.TransformColumns(#"Kept Risks", {{"Impact", each List.Max({1, List.Min({5, if _ = null then 3 else _})}), Int64.Type}, {"Likelihood", each List.Max({1, List.Min({5, if _ = null then 3 else _})}), Int64.Type}}), #"Default Status" = Table.ReplaceValue(#"Clamped", null, "Open", Replacer.ReplaceValue, {"Status"}), #"Added Score" = Table.AddColumn(#"Default Status", "Score", each [Impact] * [Likelihood], Int64.Type) in #"Added Score" Step 06: Model and date table The Date table is DAX: it reads the status date from Setup (or uses TODAY), and runs from the first project month to the later of the last finish date and ten weeks after the status date. Month Year feeds the spend curve; WeekStart and WeekOffset feed the burn-up. Mark it as a date table. Tasks, Costs and Risks relate to Projects by project name, Tasks to People through Assigned To, and Costs to Date by month, so one Department slicer filters every page. Date table in DAX Month Year and WeekOffset DAX Date = VAR _s = CALCULATE ( MAX ( Setup[Value] ), Setup[Setting] = "Status date" ) VAR _asof = IF ( ISBLANK ( _s ), TODAY (), _s ) VAR _first = MIN ( MIN ( Projects[StartDate] ), MIN ( Costs[Month] ) ) VAR _last = MAX ( MAX ( Projects[FinishDate] ), _asof + 70 ) VAR _asofWeek = _asof - WEEKDAY ( _asof, 2 ) + 1 RETURN ADDCOLUMNS ( CALENDAR ( DATE ( YEAR ( _first ), MONTH ( _first ), 1 ), EOMONTH ( _last, 0 ) ), "Year", YEAR ( [Date] ), "MonthNum", MONTH ( [Date] ), "YearMonth", YEAR ( [Date] ) * 100 + MONTH ( [Date] ), "Month Year", FORMAT ( [Date], "mmm", "en-US" ) & UNICHAR ( 10 ) & FORMAT ( [Date], "yy", "en-US" ), "MonthOffset", ( YEAR ( [Date] ) - YEAR ( _asof ) ) * 12 + MONTH ( [Date] ) - MONTH ( _asof ), "WeekStart", [Date] - WEEKDAY ( [Date], 2 ) + 1, "WeekOffset", INT ( ( [Date] - WEEKDAY ( [Date], 2 ) + 1 - _asofWeek ) / 7 ) ) Step 07: Percent complete against the plan Effort Earned multiplies each task's hours by its credit (1, 0.5 or 0), and % Complete divides it by Effort Total. Effort Planned counts only the hours of tasks due by the status date, so % Planned is where the plan says you should be. Schedule Variance is % Complete minus % Planned. Weighting by hours matters: counting tasks would give a two-hour sign-off the same weight as a forty-hour build. Effort Earned Effort Planned DAX As Of = VAR _s = CALCULATE ( MAX ( Setup[Value] ), REMOVEFILTERS (), Setup[Setting] = "Status date" ) VAR _asof = IF ( ISBLANK ( _s ), TODAY (), _s ) RETURN _asof DAX Effort Earned = SUMX ( Tasks, Tasks[Effort] * Tasks[Credit] ) DAX % Complete = DIVIDE ( [Effort Earned], [Effort Total] ) DAX Effort Planned = VAR _a = [As Of] RETURN CALCULATE ( SUM ( Tasks[Effort] ), KEEPFILTERS ( Tasks[DueDate] <= _a ) ) DAX Schedule Variance = IF ( NOT ISBLANK ( [Effort Total] ), [% Complete] - [% Planned] ) Step 08: Forecast cost and the health label Forecast Cost is spent divided by % complete, the classic earned-value estimate at completion, calculated per project with SUMX and then added up. Forecast Variance compares it with the budget. Health uses SWITCH ( TRUE () ): Done at 100%; Off track if the forecast is more than 10% over budget or the project is more than 15 points behind plan; At risk if it is over budget or more than 5 points behind; otherwise On track. Counting projects per label is a FILTER over VALUES ( Projects[Project] ). Forecast Cost Health DAX Forecast Cost = SUMX ( VALUES ( Projects[Project] ), VAR _c = [% Complete] RETURN IF ( _c > 0, DIVIDE ( [Spent], _c ), [Budget] ) ) DAX Health = VAR _c = [% Complete] VAR _sv = [Schedule Variance] VAR _b = [Budget] VAR _f = [Forecast Cost] RETURN IF ( HASONEVALUE ( Projects[Project] ) && NOT ISBLANK ( [Effort Total] ), SWITCH ( TRUE (), _c >= 0.999, "Done", ( _b > 0 && _f > _b * 1.1 ) || _sv < -0.15, "Off track", ( _b > 0 && _f > _b ) || _sv < -0.05, "At risk", "On track" ) ) DAX Projects On track = COUNTROWS ( FILTER ( VALUES ( Projects[Project] ), [Health] = "On track" ) ) + 0 Step 09: App-style design The app window, the cards and the illustrations are one 1920 x 1080 background image, set under Format page > Canvas background with Fit and 0% transparency. A theme file sets the fonts and colours and removes the background and border from every visual. Everything a user might change sits on top as a real object: the logo is an image visual, the brand name is a text box, the tabs are buttons with a page navigation action, and the Department slicer is synced across the pages. Canvas background Theme file Image visual Step 10: Cards drawn by DAX Each KPI card is one DAX measure that returns an SVG picture as text: a label, the value and a coloured pill. Set the measure's data category to Image URL and show it in an image visual; it filters like any other measure. The 3D icon on each card is a separate image visual, so it can be replaced without touching the DAX. The tall hero card works the same way: the illustration is in the background, and the numbers, health rows and budget bar are a measure on top. One measure, one picture Data category: Image URL DAX Card Forecast = VAR _pill = IF ( [Forecast Variance] > 0, "+" & IF ( ABS ( [Forecast Variance] ) >= 1000000000, FORMAT ( [Forecast Variance] / 1000000000, "$#,0.00", "en-US" ) & "B", IF ( ABS ( [Forecast Variance] ) >= 1000000, FORMAT ( [Forecast Variance] / 1000000, "$#,0.0", "en-US" ) & "M", IF ( ABS ( [Forecast Variance] ) >= 10000, FORMAT ( [Forecast Variance] / 1000, "$#,0.0", "en-US" ) & "K", FORMAT ( [Forecast Variance], "$#,0", "en-US" ) ) ) ) & " over budget", IF ( ABS ( ABS ( [Forecast Variance] ) ) >= 1000000000, FORMAT ( ABS ( [Forecast Variance] ) / 1000000000, "$#,0.00", "en-US" ) & "B", IF ( ABS ( ABS ( [Forecast Variance] ) ) >= 1000000, FORMAT ( ABS ( [Forecast Variance] ) / 1000000, "$#,0.0", "en-US" ) & "M", IF ( ABS ( ABS ( [Forecast Variance] ) ) >= 10000, FORMAT ( ABS ( [Forecast Variance] ) / 1000, "$#,0.0", "en-US" ) & "K", FORMAT ( ABS ( [Forecast Variance] ), "$#,0", "en-US" ) ) ) ) & " under budget" ) VAR _tone = IF ( [Forecast Variance] > 0, -1, 1 ) VAR _fg = SWITCH ( _tone, 1, "rgb(5,150,105)", -1, "rgb(225,29,72)", 2, "rgb(180,83,9)", "rgb(91,101,133)" ) VAR _bg = SWITCH ( _tone, 1, "rgb(227,247,238)", -1, "rgb(253,232,236)", 2, "rgb(254,243,215)", "rgb(238,241,248)" ) VAR _pw = 26 + LEN ( _pill ) * 6.9 RETURN "data:image/svg+xml;utf8,<svg xmlns='http://www.w3.org/2000/svg' width='327' height='196' viewBox='0 0 327 196'>" & "<text x='100' y='48' font-family='Segoe UI' font-size='14.5' font-weight='600' fill='rgb(17,26,58)'>Forecast at completion</text>" & "<text x='100' y='70' font-family='Segoe UI' font-size='12' fill='rgb(154,163,191)'>Spend so far / work done</text>" & "<text x='21' y='138' font-family='Segoe UI' font-size='36' font-weight='700' fill='rgb(17,26,58)'>" & IF ( ABS ( [Forecast Cost] ) >= 1000000000, FORMAT ( [Forecast Cost] / 1000000000, "$#,0.00", "en-US" ) & "B", IF ( ABS ( [Forecast Cost] ) >= 1000000, FORMAT ( [Forecast Cost] / 1000000, "$#,0.0", "en-US" ) & "M", IF ( ABS ( [Forecast Cost] ) >= 10000, FORMAT ( [Forecast Cost] / 1000, "$#,0.0", "en-US" ) & "K", FORMAT ( [Forecast Cost], "$#,0", "en-US" ) ) ) ) & "</text>" & "<rect x='22' y='154' width='" & FORMAT ( _pw, "0", "en-US" ) & "' height='26' rx='13' fill='" & _bg & "'/>" & "<text x='35' y='171.5' font-family='Segoe UI' font-size='12.5' font-weight='600' fill='" & _fg & "'>" & _pill & "</text>" & "</svg>" Step 11: Projects table and spend curve The table is the standard table visual with four SVG measures: an avatar with the owner's initials, a health label, a progress bar (fill = % complete, black tick = % planned) and a budget bar (fill = budget used, circle = forecast cost). All image measures in one table must be as wide as the table's image width, with their content drawn from the left. A table cannot sort by a picture, so each health label starts with a sort key (data-k) right after the svg tag: 3 for off track, 2 for at risk. The spend curve compares the cumulative actual cost with each budget spread evenly over its project's dates. Fill = % complete Budget used DAX Health Chip = VAR _h = [Health] VAR _hc = SWITCH ( _h, "On track", "rgb(16,185,129)", "At risk", "rgb(245,158,11)", "Off track", "rgb(244,63,94)", "Done", "rgb(79,70,229)", "rgb(203,211,230)" ) VAR _hf = SWITCH ( _h, "On track", "rgb(5,150,105)", "At risk", "rgb(180,83,9)", "Off track", "rgb(225,29,72)", "Done", "rgb(79,70,229)", "rgb(91,101,133)" ) VAR _hb = SWITCH ( _h, "On track", "rgb(227,247,238)", "At risk", "rgb(254,243,215)", "Off track", "rgb(253,232,236)", "Done", "rgb(236,235,254)", "rgb(238,241,248)" ) VAR _hk = SWITCH ( _h, "On track", 1, "At risk", 2, "Off track", 3, "Done", 0, 0 ) RETURN IF ( NOT ISBLANK ( _h ), "data:image/svg+xml;utf8,<svg xmlns='http://www.w3.org/2000/svg' data-k='" & _hk & "' width='170' height='36'>" & "<rect x='1' y='5' width='" & ( 30 + LEN ( _h ) * 7 ) & "' height='26' rx='13' fill='" & _hb & "'/>" & "<circle cx='15' cy='18' r='4.5' fill='" & _hc & "'/>" & "<text x='26' y='22.5' font-family='Segoe UI' font-size='12' font-weight='600' fill='" & _hf & "'>" & _h & "</text></svg>" ) DAX Progress Bar = VAR _c = [% Complete] VAR _p = [% Planned] VAR _w = 118 RETURN IF ( NOT ISBLANK ( [Effort Total] ), "data:image/svg+xml;utf8,<svg xmlns='http://www.w3.org/2000/svg' width='170' height='36'>" & "<text x='0' y='22.5' font-family='Segoe UI' font-size='12.5' font-weight='700' fill='rgb(17,26,58)'>" & FORMAT ( _c * 100, "0", "en-US" ) & "%25</text>" & "<rect x='44' y='14' width='" & _w & "' height='8' rx='4' fill='rgb(238,241,248)'/>" & "<rect x='44' y='14' width='" & FORMAT ( MIN ( 1, _c ) * _w, "0.0", "en-US" ) & "' height='8' rx='4' fill='rgb(37,99,235)'/>" & "<rect x='" & FORMAT ( 43 + MIN ( 1, _p ) * _w, "0.0", "en-US" ) & "' y='9' width='2.5' height='18' rx='1.2' fill='rgb(17,26,58)'/></svg>" ) DAX Mo Planned Spend = VAR _e = MAX ( 'Date'[Date] ) VAR _s0 = MIN ( 'Date'[Date] ) RETURN IF ( HASONEVALUE ( 'Date'[YearMonth] ), SUMX ( Projects, Projects[Budget] * MAX ( 0, MIN ( 1, DIVIDE ( _e - Projects[StartDate], Projects[FinishDate] - Projects[StartDate] ) ) ) ) ) Step 12: A Gantt chart without a custom visual Each row of the table is one SVG from the Gantt Project measure. It takes the range from ALLSELECTED (earliest start to latest finish, rounded out to whole months) and turns every date into an x position on the same width, so all rows share one scale. It draws month gridlines, the planned bar in a pale health colour, a solid bar as wide as % complete, a diamond for every milestone (CONCATENATEX over the milestone tasks; filled when done, red when late) and a dashed line for the status date. The month header is another SVG measure in an image visual; Edit interactions stops the table from filtering it. Pale bar + solid fill Milestones DAX Gantt Project = VAR _min = MINX ( ALLSELECTED ( Projects ), Projects[StartDate] ) VAR _max = MAXX ( ALLSELECTED ( Projects ), Projects[FinishDate] ) VAR _r0 = DATE ( YEAR ( _min ), MONTH ( _min ), 1 ) VAR _r1 = EOMONTH ( _max, 0 ) + 1 VAR _span = _r1 - _r0 VAR _a = [As Of] VAR _ax = 8 + DIVIDE ( _a - _r0, _span ) * 1164 VAR _months = FILTER ( CALENDAR ( _r0, _r1 - 1 ), DAY ( [Date] ) = 1 ) VAR _h = [Health] VAR _hc = SWITCH ( _h, "On track", "rgb(16,185,129)", "At risk", "rgb(245,158,11)", "Off track", "rgb(244,63,94)", "Done", "rgb(79,70,229)", "rgb(203,211,230)" ) VAR _hf = SWITCH ( _h, "On track", "rgb(5,150,105)", "At risk", "rgb(180,83,9)", "Off track", "rgb(225,29,72)", "Done", "rgb(79,70,229)", "rgb(91,101,133)" ) VAR _hb = SWITCH ( _h, "On track", "rgb(227,247,238)", "At risk", "rgb(254,243,215)", "Off track", "rgb(253,232,236)", "Done", "rgb(236,235,254)", "rgb(238,241,248)" ) VAR _hk = SWITCH ( _h, "On track", 1, "At risk", 2, "Off track", 3, "Done", 0, 0 ) VAR _s = MIN ( Projects[StartDate] ) VAR _f = MAX ( Projects[FinishDate] ) VAR _x0 = ( 8 + DIVIDE ( _s - _r0, _span ) * 1164 ) VAR _x1 = MAX ( _x0 + 8, ( 8 + DIVIDE ( _f + 1 - _r0, _span ) * 1164 ) ) VAR _c = [% Complete] VAR _ms = CONCATENATEX ( FILTER ( Tasks, Tasks[IsMilestone] = 1 ), VAR _mx = ( 8 + DIVIDE ( Tasks[DueDate] - _r0, _span ) * 1164 ) VAR _done = Tasks[Progress] = "Completed" VAR _late = NOT _done && Tasks[DueDate] < _a RETURN "<polygon points='" & FORMAT ( _mx, "0.0", "en-US" ) & ",9 " & FORMAT ( _mx + 7, "0.0", "en-US" ) & ",18 " & FORMAT ( _mx, "0.0", "en-US" ) & ",27 " & FORMAT ( _mx - 7, "0.0", "en-US" ) & ",18' fill='" & IF ( _done, _hc, "white" ) & "' stroke='" & IF ( _late, "rgb(244,63,94)", _hc ) & "' stroke-width='2'/>", "" ) RETURN IF ( HASONEVALUE ( Projects[Project] ) && NOT ISBLANK ( [Effort Total] ), "data:image/svg+xml;utf8,<svg xmlns='http://www.w3.org/2000/svg' data-k='" & FORMAT ( _s, "yyyymmdd" ) & "' width='1180' height='36'>" & CONCATENATEX ( _months, "<line x1='" & FORMAT ( ( 8 + DIVIDE ( [Date] - _r0, _span ) * 1164 ), "0.0", "en-US" ) & "' y1='0' x2='" & FORMAT ( ( 8 + DIVIDE ( [Date] - _r0, _span ) * 1164 ), "0.0", "en-US" ) & "' y2='36' stroke='rgb(227,232,245)' stroke-width='1'/>", "" ) & "<rect x='" & FORMAT ( _x0, "0.0", "en-US" ) & "' y='10' width='" & FORMAT ( _x1 - _x0, "0.0", "en-US" ) & "' height='16' rx='8' fill='" & _hc & "' fill-opacity='0.22'/>" & "<rect x='" & FORMAT ( _x0, "0.0", "en-US" ) & "' y='10' width='" & FORMAT ( MAX ( 0, MIN ( 1, _c ) ) * ( _x1 - _x0 ), "0.0", "en-US" ) & "' height='16' rx='8' fill='" & _hc & "'/>" & "<line x1='" & FORMAT ( _ax, "0.0", "en-US" ) & "' y1='0' x2='" & FORMAT ( _ax, "0.0", "en-US" ) & "' y2='36' stroke='rgb(244,63,94)' stroke-width='1.6' stroke-dasharray='3 3'/>" & _ms & "</svg>" ) DAX Gantt Header Projects = VAR _min = MINX ( ALLSELECTED ( Projects ), Projects[StartDate] ) VAR _max = MAXX ( ALLSELECTED ( Projects ), Projects[FinishDate] ) VAR _r0 = DATE ( YEAR ( _min ), MONTH ( _min ), 1 ) VAR _r1 = EOMONTH ( _max, 0 ) + 1 VAR _span = _r1 - _r0 VAR _a = [As Of] VAR _ax = 8 + DIVIDE ( _a - _r0, _span ) * 1164 VAR _months = FILTER ( CALENDAR ( _r0, _r1 - 1 ), DAY ( [Date] ) = 1 ) VAR _lab = CONCATENATEX ( _months, VAR _x = ( 8 + DIVIDE ( [Date] - _r0, _span ) * 1164 ) VAR _xm = ( 8 + DIVIDE ( EOMONTH ( [Date], 0 ) + 1 - _r0, _span ) * 1164 ) RETURN "<line x1='" & FORMAT ( _x, "0.0", "en-US" ) & "' y1='18' x2='" & FORMAT ( _x, "0.0", "en-US" ) & "' y2='34' stroke='rgb(227,232,245)' stroke-width='1'/>" & IF ( _xm - _x >= 30, "<text x='" & FORMAT ( ( _x + _xm ) / 2, "0.0", "en-US" ) & "' y='30' text-anchor='middle' font-family='Segoe UI' font-size='11.5' fill='rgb(91,101,133)'>" & FORMAT ( [Date], "mmm", "en-US" ) & IF ( MONTH ( [Date] ) = 1 || [Date] = _r0, " " & FORMAT ( [Date], "yy", "en-US" ), "" ) & "</text>", "" ), "" ) RETURN IF ( NOT ISBLANK ( _min ), "data:image/svg+xml;utf8,<svg xmlns='http://www.w3.org/2000/svg' width='1180' height='36'>" & _lab & "<rect x='" & FORMAT ( _ax - 26, "0.0", "en-US" ) & "' y='0' width='52' height='16' rx='8' fill='rgb(244,63,94)'/>" & "<text x='" & FORMAT ( _ax, "0.0", "en-US" ) & "' y='12' text-anchor='middle' font-family='Segoe UI' font-size='10' font-weight='700' fill='white'>Today</text></svg>" ) Step 13: Workload, risks and burn-up Load Hours adds the remaining hours of every open task due within 28 days of the status date (overdue ones included, in-progress tasks at half) and compares them with four weeks of each person's capacity. The bar has a tick at 100%. The heat map is one SVG measure with 25 cells, each counting the open risks with that impact and likelihood. The burn-up compares tasks completed by each week with tasks due by that week, against the total scope. Priya Nair: 149% Load Hours DAX Load Hours = VAR _a = [As Of] RETURN SUMX ( FILTER ( Tasks, Tasks[Progress] <> "Completed" && Tasks[DueDate] <= _a + 28 ), Tasks[Effort] * ( 1 - Tasks[Credit] ) ) DAX Load % = DIVIDE ( [Load Hours], [Capacity 4 Weeks] ) DAX Wk Done = VAR _o = MAX ( 'Date'[WeekOffset] ) VAR _we = MAX ( 'Date'[WeekStart] ) + 6 RETURN IF ( HASONEVALUE ( 'Date'[WeekStart] ) && _o >= -30 && _o <= 10, IF ( _o <= 0, CALCULATE ( COUNTROWS ( Tasks ), KEEPFILTERS ( NOT ISBLANK ( Tasks[CompletedDate] ) && Tasks[CompletedDate] <= _we ) ) + 0 ) ) Step 14: Update it, then rebalance the team When tasks are finished, set Progress to Completed and fill in Completed Date, save and click Refresh. Overdue tasks and % complete update, but a project can stay off track because its forecast cost is driven by money already spent. To fix an overload, reassign a task or move its due date in Excel and refresh: the workload bars show the effect straight away. Three tasks completed Overdue: 16 > 13 Still off track Wrap-up You've now built a complete project management dashboard: forgiving Power Query steps for a Planner-style task export, a small star schema with a DAX date table, hours-weighted progress against plan, an earned-value forecast and a health label, and an app-style design with a Gantt chart drawn inside a standard table. If you'd rather start from the finished file, the ready-made template includes the dashboard, the Excel workbook and a step-by-step setup guide. Prefer the ready-made template? Get Project Management for $19, with the dashboard, the Excel file and a step-by-step guide: Click to Buy More dashboards: https://www.youtube.com/@PivotalstatsProjects · https://pivotalstats.com
